appdept=@appdept and @cond --not a logical expression
CREATE function f_1(@appdept VARCHAR(20),@cond varchar(1000) ) returns varchar(8000) as begin declare @str varchar(8000) --declare @cond varchar(1000) set @str='' select @str=@str+','+convert(varchar(20), id) from bgyp_app where appdept=@appdept and appdept=@cond set @str=right(@str,len(@str)-1) return(@str) End
如果不是动态语句,不能这样 where appdept=@appdept and @cond改为如: where appdept=@appdept and cond=@cond
回以上的话,@cond为一条件。不行的话那我就只能改改了。 多谢谢!
...and @cond 改为 exec('... and '+@cond)
@cond为条件是sql语句不能这样写了,会出错。可以改成以下的方式: CREATE function f_1(@appdept VARCHAR(20),@cond varchar(1000) ) returns varchar(8000) as begin declare @str varchar(8000) declare @command varchar(8000) --declare @cond varchar(1000) set @str='' set @command='select @str=@str+'',''+convert(varchar(20), id) from bgyp_app where appdept='+@appdept+' and '+@cond --select @str=@str+','+convert(varchar(20), id) from bgyp_app where appdept=@appdept and @cond exec (@command) set @str=right(@str,len(@str)-1) return(@str) End
你可以建个存储过程来执行字符串。 CREATE PROCEDURE execcommand @command varchar(8000) AS exec (@command) GO 然后,在函数中用 CREATE function f_1(@appdept VARCHAR(20),@cond varchar(1000) ) returns varchar(8000) as begin declare @str varchar(8000) declare @command varchar(8000) --declare @cond varchar(1000) set @str='' set @command='select @str=@str+'',''+convert(varchar(20), id) from bgyp_app where appdept='+@appdept+' and '+@cond --select @str=@str+','+convert(varchar(20), id) from bgyp_app where appdept=@appdept and @cond exec execcommand @command set @str=right(@str,len(@str)-1) return(@str) End 在试试看看!
returns varchar(8000)
as
begin
declare @str varchar(8000)
--declare @cond varchar(1000)
set @str=''
select @str=@str+','+convert(varchar(20), id) from bgyp_app where appdept=@appdept and appdept=@cond
set @str=right(@str,len(@str)-1)
return(@str)
End
where appdept=@appdept and @cond改为如:
where appdept=@appdept and cond=@cond
多谢谢!
改为 exec('... and '+@cond)
CREATE function f_1(@appdept VARCHAR(20),@cond varchar(1000) )
returns varchar(8000)
as
begin
declare @str varchar(8000)
declare @command varchar(8000)
--declare @cond varchar(1000)
set @str=''
set @command='select @str=@str+'',''+convert(varchar(20), id) from bgyp_app where appdept='+@appdept+' and '+@cond
--select @str=@str+','+convert(varchar(20), id) from bgyp_app where appdept=@appdept and @cond
exec (@command)
set @str=right(@str,len(@str)-1)
return(@str)
End
exec (@command),是这一句。“在函数内不正确的使用了execute”
CREATE PROCEDURE execcommand @command varchar(8000)
AS
exec (@command)
GO
然后,在函数中用
CREATE function f_1(@appdept VARCHAR(20),@cond varchar(1000) )
returns varchar(8000)
as
begin
declare @str varchar(8000)
declare @command varchar(8000)
--declare @cond varchar(1000)
set @str=''
set @command='select @str=@str+'',''+convert(varchar(20), id) from bgyp_app where appdept='+@appdept+' and '+@cond
--select @str=@str+','+convert(varchar(20), id) from bgyp_app where appdept=@appdept and @cond
exec execcommand @command
set @str=right(@str,len(@str)-1)
return(@str)
End
在试试看看!