怎么将下面的字符串当做存储过程的一个参数传到存储过程里去,并能执行in 'qwe','5656','545','344','233','123'如 create proc test(@time) as begin select * from tb where id in(@time) endexec test 'qwe','5656','545','344','233','123'--将字符串当做@time参数传进去,要怎么做?
create proc test(@time) as begin exec('select * from tb where id in('+@time+')') end go 傳參加上引號
本帖最后由 roy_88 于 2011-10-20 13:46:55 编辑
或create proc test(@time) as begin select * from tb where ','+@time)+',' LIKE '%,'+RTRIM(ID)+',%' end GO EXEC test 'qwe,5656,545,344,233,123'
表名或者字段名为变量的时候 需要用动态语句create proc test(@time) as begin exec('select * from tb where id in('+@time+')') end go exec test '''qwe'',''5656'',''545'',''344'',''233'',''123'''
cte中貌似不能用动态语句,看来要大改
可以建一个临时表,根据传入的参数进行动态查询,放到临时表中去,然后再在临时表的基础上做cte.
CTE与语句一样用记录如果CTE前用语句加上;with a as (select 1 as ID) select * from a
declare @execSQL NVARCHAR(4000) declare @param NVARCHAR(1000) declare @origexecSQL nvarchar(4000)set @origexecSQL=N'select * from t1 where flowno in (%idset%)' set @param=N'@idset varchar(20) 'set @execSQL=REPLACE(@origexecSQL, '%idset%', '1,2,3') print @execSQL set @execSQL=REPLACE(@origexecSQL, '%idset%', ' ''1'',''2'',''3'' ') print @execSQLexec(@execSQL)做一段小的字符串替换即可
if object_id('test','P') is not null drop procedure test go create proc test(@time varchar(100)) as exec('select * from tb where id in('+@time+')') goexec test '''101'',''102'''
create proc test(@time) as begin exec('select * from tb where id in('+@time+')') end go exec test '''qwe'',''5656'',''545'',''344'',''233'',''123'''
as
begin
exec('select * from tb where id in('+@time+')')
end
go
傳參加上引號
as
begin
select * from tb where ','+@time)+',' LIKE '%,'+RTRIM(ID)+',%'
end
GO
EXEC test 'qwe,5656,545,344,233,123'
as
begin
exec('select * from tb where id in('+@time+')')
end
go
exec test '''qwe'',''5656'',''545'',''344'',''233'',''123'''
cte中貌似不能用动态语句,看来要大改
可以建一个临时表,根据传入的参数进行动态查询,放到临时表中去,然后再在临时表的基础上做cte.
CTE与语句一样用记录如果CTE前用语句加上;with a as (select 1 as ID) select * from a
declare @execSQL NVARCHAR(4000)
declare @param NVARCHAR(1000)
declare @origexecSQL nvarchar(4000)set @origexecSQL=N'select * from t1 where flowno in (%idset%)'
set @param=N'@idset varchar(20) 'set @execSQL=REPLACE(@origexecSQL, '%idset%', '1,2,3')
print @execSQL
set @execSQL=REPLACE(@origexecSQL, '%idset%', ' ''1'',''2'',''3'' ')
print @execSQLexec(@execSQL)做一段小的字符串替换即可
if object_id('test','P') is not null
drop procedure test
go
create proc test(@time varchar(100))
as
exec('select * from tb where id in('+@time+')')
goexec test '''101'',''102'''
as
begin
exec('select * from tb where id in('+@time+')')
end
go
exec test '''qwe'',''5656'',''545'',''344'',''233'',''123'''