--这样应该不行吧!你用临时表试试,也就是用临时表#tb来保存返回的值,再通过select语句把值给@OndutyIDs
CREATE PROCEDURE GetOndutyListOfAccMembers1ByDate
@SomeDay Datetime,
@TableField1 varchar(50),
@TableField2 varchar(50),
@TableName varchar(50)AS
create table #tb(ondutyid varchar(1000))
declare @OndutyIDs varchar(1000)
insert #tb exec(' select '+@TableField1+' from '+@TableName+' where '+@TableField2+'='+@SomeDay )
select @ondutyIDs=ondutyid from #tb
select distinct EmpID,Name from
Employee
where charindex(Convert(char(7),EmpID),@OndutyIDs)>0
GO
CREATE PROCEDURE GetOndutyListOfAccMembers1ByDate
@SomeDay Datetime,
@TableField1 varchar(50),
@TableField2 varchar(50),
@TableName varchar(50)AS
create table #tb(ondutyid varchar(1000))
declare @OndutyIDs varchar(1000)
insert #tb exec(' select '+@TableField1+' from '+@TableName+' where '+@TableField2+'='+@SomeDay )
select @ondutyIDs=ondutyid from #tb
select distinct EmpID,Name from
Employee
where charindex(Convert(char(7),EmpID),@OndutyIDs)>0
GO
我改了一下,如下:
CREATE PROCEDURE GetOndutyListByDate
@SomeDay Datetime,
@TableField1 varchar(50),
@TableField2 varchar(50),
@TableName varchar(50)AS
create table #tb(ondutyid varchar(1000))
declare @OndutyIDs varchar(1000)
insert #tb exec(' select '+@TableField1+' from '+@TableName+' where datediff(day, '+@TableField2+','+@SomeDay+')=0' )
select @ondutyIDs=ondutyid from #tb
select distinct EmpID,Name from
Employee
where charindex(Convert(char(7),EmpID),@OndutyIDs)>0
GO语法是没错误了.用查询分析器执行测试,GetOndutyListByDate '2003-4-23',members1 ,workdate ,accworklog
结果:
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '23' 附近有语法错误。(所影响的行数为 0 行)
请问怎样测试这个存储过程的正确性?
试试
不过还是不行,比如@TableField2的值是"WorkDate",因为传入的字符型,提示
将数据类型 nvarchar 转换为 datetime 时出错。
@SomeDay Datetime,
@TableField1 varchar(50),
@TableField2 varchar(50),
@TableName varchar(50)AS
declare @OndutyIDs varchar(1000)
set @OndutyIDs = 'select '+@TableField1+' from '+@TableName+' where '+@TableField2+'='+''''+Convert(varchar(40),@SomeDay)+''''
exec (@OndutyIDs)
select distinct Emp_ID,fName from Employee where charindex(Convert(char(7),Emp_ID),@OndutyIDs)>0
存储过程如下:
CREATE PROCEDURE GetOndutyListByDate
@SomeDay Datetime,
@SelectField varchar(50),
@WhereField varchar(50),
@TableName varchar(50)AS
create table #tb(ondutyid varchar(1000))
declare @OndutyIDs varchar(1000)
insert #tb exec(' select '+@SelectField+' from '+@TableName+' where datediff(day,'+@WhereField+','+''''+@SomeDay+''''+')=0' )
select @ondutyIDs=ondutyid from #tb
select distinct EmpID,Name from
Employee
where charindex(Convert(char(7),EmpID),@OndutyIDs)>0
GO