在储存过程中 如果有参数
@id那么
SELECT *
FROM table
WHERE id = @id
是可以的但如果 参数是 @ids 【是多个值】
那么
SELECT *
FROM table
WHERE id in ( @ids )
或
SELECT *
FROM table
WHERE id in @ids
都有问题!
在线等。
@id那么
SELECT *
FROM table
WHERE id = @id
是可以的但如果 参数是 @ids 【是多个值】
那么
SELECT *
FROM table
WHERE id in ( @ids )
或
SELECT *
FROM table
WHERE id in @ids
都有问题!
在线等。
declare @sql varchar(200)
set @sql='SELECT * FROM table WHERE id in ( '+@ids+' ) '
exec (@sql)这个试试
CREATE TABLE test (Id int Identity(1,1), name VARCHAR(15));
INSERT INTO test(name) values('luoyoumou1');
INSERT INTO test(name) values('luoyoumou2');
INSERT INTO test(name) values('luoyoumou3');
INSERT INTO test(name) values('luoyoumou4');CREATE PROCEDURE Test_multpara @Ids VARCHAR(4000)
AS
/*
EXEC Test_multpara '1,2,3'
*/
DECLARE @SQL VARCHAR(4000)BEGIN--SET @All_Id=REPLACE(@ID,',',' OR ')
IF(ISNULL(@Ids,'')<>'')
SET @SQL='SELECT * FROM test WHERE Id IN ('+@IDs+')'
ELSE
SET @SQL='SELECT * FROM test 'EXEC (@SQL)END
INSERT INTO test(name) values('luoyoumou1');
INSERT INTO test(name) values('luoyoumou2');
INSERT INTO test(name) values('luoyoumou3');
INSERT INTO test(name) values('luoyoumou4');CREATE PROCEDURE Test_multpara @Ids VARCHAR(4000)
AS
/*
EXEC Test_multpara '1,2,3'
*/
DECLARE @SQL VARCHAR(4000)BEGINIF(ISNULL(@Ids,'')<>'')
SET @SQL='SELECT * FROM test WHERE Id IN ('+@IDs+')'
ELSE
SET @SQL='SELECT * FROM test 'EXEC (@SQL)END/code]
CREATE TABLE test (Id int Identity(1,1), name VARCHAR(15));
INSERT INTO test(name) values('luoyoumou1');
INSERT INTO test(name) values('luoyoumou2');
INSERT INTO test(name) values('luoyoumou3');
INSERT INTO test(name) values('luoyoumou4');CREATE PROCEDURE Test_multpara @Ids VARCHAR(4000)
AS
/*
EXEC Test_multpara '1,2,3'
*/
DECLARE @SQL VARCHAR(4000)BEGINIF(ISNULL(@Ids,'')<>'')
SET @SQL='SELECT * FROM test WHERE Id IN ('+@IDs+')'
ELSE
SET @SQL='SELECT * FROM test 'EXEC (@SQL)END
@ids = '11','12','22','33','44'======具体代码如下, 主要是要用在游标里面
CREATE PROCEDURE sp_select_BillPlaceEmptybyType
@ids as varchar(1000) --编号 举例: @ids = '1','2','3','4'
AS
BEGIN
DECLARE cursor1 CURSOR FOR
SELECT columns
FROM table
WHERE id IN (@ids) -- 这里存在问题!!! OPEN cursor1 FETCH NEXT FROM cursor1
INTO @xxx WHILE @@FETCH_STATUS = 0
BEGIN
-- Get the next author.
FETCH NEXT FROM cursor1
INTO @xxx
END
CLOSE cursor1
DEALLOCATE cursor1
END