存储过程传参

[yubofighting]

怎么将下面的字符串当做存储过程的一个参数传到存储过程里去，并能执行in
'qwe','5656','545','344','233','123'

如
create proc test(@time)
as
begin
	select * from tb where id in(@time)
end

exec test 'qwe','5656','545','344','233','123'--将字符串当做@time参数传进去，要怎么做？

[中国风]

create proc test(@time)
as
begin
exec('select * from tb where id in('+@time+')')
end
go
傳參加上引號

[中国风]

create proc test(@time)
as
begin
  exec('select * from tb where id in('+@time+')')
end
go
exec test  '''qwe'',''5656'',''545'',''344'',''233'',''123'''

[中国风]

或



create proc test(@time)
as
begin
    select * from tb where ','+@time)+',' LIKE '%,'+RTRIM(ID)+',%'
end


GO
EXEC test 'qwe,5656,545,344,233,123'

[cjh200102]

楼上不错。

[--小F--]

表名或者字段名为变量的时候 需要用动态语句

create proc test(@time)
as
begin
  exec('select * from tb where id in('+@time+')')
end
go
exec test  '''qwe'',''5656'',''545'',''344'',''233'',''123'''

[yubofighting]

[Quote=引用 5 楼 fredrickhu 的回复:]
SQL code
表名或者字段名为变量的时候 需要用动态语句

create proc test(@time)
as
begin
exec('select * from tb where id in('+@time+')')
end
go
exec test '''qwe'',''5656'',''545'',''344'',''233'',''123'''
[/Quote]

cte中貌似不能用动态语句，看来要大改。。。

[-晴天]

[Quote=引用 6 楼 yubofighting 的回复:]
引用 5 楼 fredrickhu 的回复:
SQL code
表名或者字段名为变量的时候 需要用动态语句

create proc test(@time)
as
begin
exec('select * from tb where id in('+@time+')')
end
go
exec test '''qwe'',''5656'',''545'',''344'',''2……
[/Quote]

可以建一个临时表,根据传入的参数进行动态查询,放到临时表中去,然后再在临时表的基础上做cte.

[中国风]

[Quote=引用 6 楼 yubofighting 的回复:]

引用 5 楼 fredrickhu 的回复:
SQL code
表名或者字段名为变量的时候 需要用动态语句

create proc test(@time)
as
begin
exec('select * from tb where id in('+@time+')')
end
go

exec test '''qwe'',''5656'',''545'',''344'','……
[/Quote]

CTE与语句一样用

记录如果CTE前用语句加上;with a as (select 1 as ID) select * from a

[小宏]

[Quote=引用 5 楼 fredrickhu 的回复:]
SQL code
表名或者字段名为变量的时候 需要用动态语句

create proc test(@time)
as
begin
exec('select * from tb where id in('+@time+')')
end
go
exec test '''qwe'',''5656'',''545'',''344'',''233'',''123'''
[/Quote]
+++

[lnmhfeng]

[Quote=引用 9 楼 happyandsad 的回复:]

引用 5 楼 fredrickhu 的回复:
SQL code
表名或者字段名为变量的时候 需要用动态语句

create proc test(@time)
as
begin
exec('select * from tb where id in('+@time+')')
end

go
exec test '''qwe'',''5656'',''545'',''344'','……
[/Quote]
+++

[kensouterry]

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 @execSQL

exec(@execSQL)

做一段小的字符串替换即可

[pengxuan]

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+')')
go

exec test '''101'',''102'''

[Rotel-刘志东]

create proc test(@time)
as
begin
  exec('select * from tb where id in('+@time+')')
end
go
exec test  '''qwe'',''5656'',''545'',''344'',''233'',''123'''