declare @p as varchar(200) set @p='(1,2,3,4)' exec 'select * from t where c in '+@p
declare @p varchar(100) set @p='1,2,3,4' exec ('select * from t where c in ('+@p+')')
--测试: declare @p varchar(100) set @p='1,2,3,4' exec ('select * from sysobjects where id in ('+@p+')')
create table t(a int) insert into t select 5 union select 3 union select 2 union select 7declare @p varchar(200) declare @s varchar(200) set @p='(1,2,3,4)' set @s= 'select * from t where a in '+@p exec (@s) drop table t --------------------------- a 2 3 ---------------------------- (2 行受影响)
--测试:declare @p varchar(100) set @p='1,2,3,4' exec ('select * from sysobjects where id in ('+@p+')')-- OR set @p=replace(@p,',',' or id=') exec ('select * from sysobjects where id='+@p)
declare @id varchar(1000) declare @sql nvarchar(4000) set @id='1,2,4,5,6,7,8,9,10' set @sql=N'select * from product where proid in ('+@id +')' exec sp_executesql @sql
CREATE PROCEDURE my_proc @p varchar(100) AS exec('select * from t where c in ' + @p) GOexec my_proc '(1,2,3,4)'
http://topic.csdn.net/t/20031031/12/2413959.htmlCREATE PROCEDURE GetRoles (@RoleList nvarchar(4000)) as declare @temptbl table(rname nvarchar(100)) declare @rlist nvarchar(4000) declare @pos tinyint set @rlist=coalesce(@RoleList,'') if @rlist<>'' begin while patindex('%,%',@rlist)<>0 begin select @pos=patindex('%,%',@rlist) insert into @temptbl(rname) values(left(@rlist,@pos-1)) select @rlist=stuff(@rlist,1,@pos,'') end end select * from Role where rolename in(select rname from @temptbl) go
set @p='(1,2,3,4)'
exec 'select * from t where c in '+@p
set @p='1,2,3,4'
exec ('select * from t where c in ('+@p+')')
declare @p varchar(100)
set @p='1,2,3,4'
exec ('select * from sysobjects where id in ('+@p+')')
insert into t select 5
union select 3
union select 2
union select 7declare @p varchar(200)
declare @s varchar(200)
set @p='(1,2,3,4)'
set @s= 'select * from t where a in '+@p
exec (@s)
drop table t
---------------------------
a
2
3
----------------------------
(2 行受影响)
set @p='1,2,3,4'
exec ('select * from sysobjects where id in ('+@p+')')-- OR
set @p=replace(@p,',',' or id=')
exec ('select * from sysobjects where id='+@p)
这样是不是和我在程序里面组合好传过来效率一样?先吃饭去,吃完看有没有更好的办法
declare @sql nvarchar(4000)
set @id='1,2,4,5,6,7,8,9,10'
set @sql=N'select * from product where proid in ('+@id +')'
exec sp_executesql @sql
@p varchar(100)
AS
exec('select * from t where c in ' + @p)
GOexec my_proc '(1,2,3,4)'
declare @temptbl table(rname nvarchar(100))
declare @rlist nvarchar(4000)
declare @pos tinyint
set @rlist=coalesce(@RoleList,'')
if @rlist<>''
begin
while patindex('%,%',@rlist)<>0
begin
select @pos=patindex('%,%',@rlist)
insert into @temptbl(rname) values(left(@rlist,@pos-1))
select @rlist=stuff(@rlist,1,@pos,'')
end
end
select * from Role where rolename in(select rname from @temptbl)
go