100分,求"列名无效"解决方法!--存储过程
----------------------------------------
--建表
----------------------------------------
CREATE TABLE [dbo].[oa_alarm_center] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[flowId] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[flowType] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[title] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[isRead] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[status] [int] NULL ,
[insertDT] [datetime] NULL ,
[sign] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[endDate] [datetime] NULL ,
[toWhom] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
----------------------------------------------
--建存储过程
----------------------------------------------
create procedure oa_alarm_getNewRecordData
(@userId int,
@userName nvarchar(50),
@userDepartment int,
@userPosition int)
as
--select * from oa_alarm_center where ', '+isRead+ ', ' not like '%, '+cast(@userId as nvarchar)+ ',% ' and status <> 2 and status <> 3
-----------------------定义变量
declare @mySql nvarchar(1000)
declare @exeSql nvarchar(1000)
set @mySql= 'select * from oa_alarm_center where ' ', ' '+isRead+ ' ', ' ' not like ' '%, ' '+cast( '+@userId+ ' as nvarchar)+ ' ',% ' ' and status <> 2 and status <> 3 '
set @exeSql= ' '
--------------------------------------------------管理员,BOSS,经理,财务部,生管部
if(@userPosition=-1 or @userPosition=0 or @userPosition=3 or @userDepartment=5 or @userDepartment=10)
begin
set @exeSql=@mySql
if(@exeSql <> ' ') exec(@exeSql)
end
---------------------------------------------------业务部
else if(@userDepartment=12)
begin
if(@userPosition=2)--业务部主管
begin
set @exeSql=case when flowType= 'orderFlow ' then @mySql when flowType= 'makeFlow ' then @mySql when flowType= 'sheetBuy ' then @mySql else ' ' end
if(@exeSql <> ' ') exec(@exeSql)
end
else--普通业务员
begin
set @exeSql=case when flowType= 'orderFlow ' then @mySql+ ' and [name]= '+@userName when flowType= 'makeFlow ' then @mySql when flowType= 'sheetBuy ' then @mySql else ' ' end
if(@exeSql <> ' ') exec(@exeSql)
end
end
----------------------------------------------采购部
else if(@userDepartment=8)
begin
if(@userPosition=2)--采购部主管
begin
set @exeSql=case when flowType= 'buyFlow ' then @mySql when flowType= 'makeFlow ' then @mySql when flowType= 'sheetBuy ' then @mySql else ' ' end
if(@exeSql <> ' ') exec(@exeSql)
end
else--普通采购员
begin
set @exeSql=case when flowType= 'buyFlow ' then @mySql+ ' and ' ', ' '+toWhom+ ' ', ' ' like ' '%, '+@userName+ ',% ' ' ' when flowType= 'makeFlow ' then @mySql when flowType= 'sheetFlow ' then @mySql else ' ' end
if(@exeSql <> ' ') exec(@exeSql)
end
end
----------------------------------------------------仓库部
else if(@userDepartment=2)
begin
set @exeSql=case when flowType= 'buyFlow ' then @mySql when flowType= 'makeFlow ' then @mySql when flowType= 'sheetBuy ' then @mySql else ' ' end
if(@exeSql <> ' ') exec(@exeSql)
end
----------------------------------------------------其它
else
begin
set @exeSql=case when flowType= 'makeFlow ' then @mySql when flowType= 'sheetBuy ' then @mySql else ' ' end
if(@exeSql <> ' ') exec(@exeSql)
end
go
------------------------------------------------
--提示列名 'flowType ' 无效。
--跪求解决方法
--感谢!
------------------------------------------------
[解决办法]
set @mySql= 'select * from oa_alarm_center where ' ', ' '+isRead+ ' ', ' ' not like ' '%, ' '+cast( '+@userId+ ' as nvarchar)+ ' ',% ' ' and status <> 2 and status <> 3 '
改為
set @mySql= 'select * from oa_alarm_center where ' ', ' '+isRead+ ' ', ' ' not like ' '%, '+cast(@userId as nvarchar)+ ',% ' ' and status <> 2 and status <> 3 ' --此句需要做修改
[解决办法]
复制语句要用 ' '引起来
[解决办法]
--改一处：
/*
if(@userPosition=2)--业务部主管
begin
set @exeSql=case when flowType= 'orderFlow ' then @mySql when flowType= 'makeFlow ' then @mySql when flowType= 'sheetBuy ' then @mySql else ' ' end
if(@exeSql <> ' ') exec(@exeSql)
end
*/
if(@userPosition=2)--业务部主管
begin
set @exeSql = @mySql + ' and flowType in( ' 'orderFlow ' ', ' 'makeFlow ' ', ' 'sheetBuy ' ') '
exec(@exeSql)
end
[解决办法]
有一点:当需要给SQL语句中的变量加上单引号的时候要多加一个,可以试试print ' ' ' '
set @exeSql=case when flowType= 'orderFlow ' then @mySql when flowType= 'makeFlow ' then @mySql when flowType= 'sheetBuy ' then @mySql else ' ' end
看看你这边
