过程声明列表

我的 SQL 代码相当简单，我试图从数据库中选择一些数据，如下所示:

SELECT * FROM DBTable
WHERE id IN (1,2,5,7,10)

我想知道如何在 select 之前声明 list (在变量、 list、 array 或者其他内容中) ，并且在 select 内部只使用变量名，比如:

VAR myList = "(1,2,5,7,10)"
SELECT * FROM DBTable
WHERE id IN myList
227788 次浏览
小开

That is not possible with a normal query since the in clause needs separate values and not a single value containing a comma separated list. One solution would be a dynamic query

declare @myList varchar(100)
set @myList = '1,2,5,7,10'
exec('select * from DBTable where id IN (' + @myList + ')')
小开
最佳答案

You could declare a variable as a temporary table like this:

declare @myList table (Id int)

Which means you can use the insert statement to populate it with values:

insert into @myList values (1), (2), (5), (7), (10)

Then your select statement can use either the in statement:

select * from DBTable
where id in (select Id from @myList)

Or you could join to the temporary table like this:

select *
from DBTable d
join @myList t on t.Id = d.Id

And if you do something like this a lot then you could consider defining a user-defined table type so you could then declare your variable like this:

declare @myList dbo.MyTableType
小开

You can convert the list of passed values into a table valued parameter and then select against this list 

DECLARE @list NVARCHAR(MAX)
SET @list = '1,2,5,7,10';


DECLARE @pos INT
DECLARE @nextpos INT
DECLARE @valuelen INT
DECLARE @tbl TABLE (number int NOT NULL)


SELECT @pos = 0, @nextpos = 1;


WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos;
END


SELECT * FROM DBTable WHERE id IN (SELECT number FROM @tbl);

In this example the string passed in '1,2,5,7,10' is split by the commas and each value is added as a new row within the @tbl table variable. This can then be selected against using standard SQL.

If you intend to reuse this functionality you could go further and convert this into a function.

小开

If you want input comma separated string as input & apply in in query in that then you can make Function like: 

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)


select @idx = 1
if len(@String)<1 or @String is null  return


while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String


if(len(@slice)>0)
insert into @temptable(Items) values(@slice)


set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;

You can use it like :

Declare @Values VARCHAR(MAX);


set @Values ='1,2,5,7,10';
Select * from DBTable
Where id  in (select items from [dbo].[Split] (@Values, ',') )

Alternatively if you don't have comma-separated string as input, You can try Table variable OR TableType Or Temp table like: INSERT using LIST into Stored Procedure

小开

I've always found it easier to invert the test against the list in situations like this. For instance...

SELECT
field0, field1, field2
FROM
my_table
WHERE
',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'

这意味着您要查找的值不需要复杂的大杂烩。

As an example, if our list was ('1,2,3'), then we add a comma to the start and end of our list like so: ',' + @mysearchlist + ','.

We also do the same for the field value we're looking for and add wildcards: '%,' + CAST(field3 AS VARCHAR) + ',%' (notice the % and the , characters).

Finally we test the two using the LIKE operator: ',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'.

小开

Alternative to @Peter Monks.

If the number in the 'in' statement is small and fixed.

DECLARE @var1 varchar(30), @var2 varchar(30), @var3  varchar(30);


SET @var1 = 'james';
SET @var2 = 'same';
SET @var3 = 'dogcat';


Select * FROM Database Where x in (@var1,@var2,@var3);

提交答案