100分,求"列名无效"解决办法!存储过程
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
----------------------------------------
--建表
----------------------------------------
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