27,579
社区成员
发帖
与我相关
我的任务
分享
alter PROCEDURE text @cs VARCHAR(400) AS
BEGIN
EXEC('INSERT INTO table1 SELECT * FROM table2 WHERE cs IN ' +@cs)
END
exec text '(02,03)'
alter PROCEDURE proc_test @cs VARCHAR(400) AS
BEGIN
EXEC sp_executesql N'INSERT INTO #temp1 SELECT * FROM #temp2 WHERE tkey IN (@cs)', N'@cs VARCHAR(400)', @cs
END
EXEC proc_test '1'
EXEC proc_test '1,2,3'--错误
--定义的存储过程只能传一个参数
alter PROCEDURE proc_test @cs VARCHAR(400) AS
BEGIN
DECLARE @sql VARCHAR(8000)
SET @sql = 'INSERT INTO #temp1 SELECT * FROM #temp2 WHERE tkey IN (' + @cs + ')'
--PRINT @sql
EXEC(@sql)
END
EXEC proc_test '''01'', ''02'', ''03'''
CREATE PROCEDURE text @cs VARCHAR(400) AS
BEGIN
INSERT INTO TABLE1 SELECT * FROM TABLE2 WHERE charindex(','+cs+',',','+@cs+',')>0
END
go
exec text '01,02'
create table TABLE2 (cs varchar(10),id int )
insert into TABLE2(cs,id)
select '01','1'
union all
select '03','3'
ALTER PROCEDURE text @cs VARCHAR(400) AS
BEGIN
declare @sql nvarchar(4000)
set @sql =N' INSERT INTO TABLE1 SELECT * FROM TABLE2 WHERE cs IN ('+@cs+')'
PRINT @sql
EXECUTE sp_executesql @sql
END
exec text '''01'',''02'''
select * from TABLE1
/*
cs id
---------- -----------
01 1
(1 行受影响)
*/
CREATE PROCEDURE text
@cs VARCHAR(400) AS
BEGIN
INSERT INTO TABLE1 SELECT * FROM TABLE2 WHERE charindex(','+ltrim(cs)+',',','+@cs+',')>0 -- cs 为int 型
END
go
exec text '1,2,3'
exec text '''01'',''02'''
这样不可以?