存储过程中用 where in
使用int类型是
CREATE Procedure [dbo].[TEST]
(
@ids int
)
AS
SELECT id,name FROM test WHERE id in (@ids)
return
Go
exec TEST 1,2,3
这样不行!
CREATE Procedure [dbo].[TEST]
(
@ids nvarchar(4000)
)
AS
SELECT id,name FROM test WHERE id in (cast(@ids as int))
return
GO
exec Test '1,3,4 '
也不行!
请教大侠了?
[解决办法]
CREATE Procedure [dbo].[TEST]
(
@ids nvarchar(4000)
)
AS
SELECT id,name FROM test WHERE CharIndex( ', ' + Rtrim(id) + ', ', ', ' + @ids + ', ') > 0
return
GO
exec Test '1,3,4 '
[解决办法]
CREATE Procedure [dbo].[TEST]
(
@ids nvarchar(4000)
)
AS
declare @sql varchar(8000)
set @sql= 'SELECT id,name FROM test WHERE id in ( '+@ids+ ');
exec(@sql)
return
GO
exec Test '1,3,4 '
[解决办法]
CREATE Procedure [dbo].[TEST]
(
@ids varchar(1000)
)
AS
declare @sql varchar(8000)
select @sql= 'SELECT id,name FROM test WHERE id in ( '+@ids + ') '
exec(@sql)
return
GO
exec Test '1,3,4 '
---try
