SQL变量保存整数列表

我试图调试别人的SQL报表，并已将底层报表查询放入SQL 2012的查询窗口。

报告要求的参数之一是一个整数列表。这是通过一个多选下拉框在报表上实现的。报告的底层查询在where子句中使用这个整数列表。

select *
from TabA
where TabA.ID in (@listOfIDs)

我不想修改我正在调试的查询，但我不知道如何在SQL Server上创建一个变量，可以保存这种类型的数据来测试它。

如。

declare @listOfIDs int
set listOfIDs  = 1,2,3,4

没有可以保存整数列表的数据类型，因此如何在SQL Server上使用与报表相同的值运行报表查询?

484158 次浏览
小开

你是对的，在SQL-Server中没有数据类型可以保存整数列表。但是你能做的是将一个整数列表存储为字符串。

DECLARE @listOfIDs varchar(8000);
SET @listOfIDs = '1,2,3,4';

然后可以将字符串拆分为单独的整数值，并将它们放入表中。您的过程可能已经做到了这一点。

你也可以使用动态查询来获得相同的结果:

DECLARE @SQL nvarchar(8000);


SET @SQL = 'SELECT * FROM TabA WHERE TabA.ID IN (' + @listOfIDs + ')';
EXECUTE (@SQL);

注意:我没有对这个查询做任何卫生处理，请注意它很容易受到SQL注入的攻击。按要求清洁。

小开

您不能这样做，但可以执行将其存储在变量中的整个查询。

例如:

DECLARE @listOfIDs NVARCHAR(MAX) =
'1,2,3'


DECLARE @query NVARCHAR(MAX) =
'Select *
From TabA
Where TabA.ID in (' + @listOfIDs + ')'


Exec (@query)
小开

表变量

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(1),(2),(3);


select *
from TabA
where TabA.ID in (select id from @listOfIDs)

或

declare @listOfIDs varchar(1000);
SET @listOfIDs = ',1,2,3,'; --in this solution need put coma on begin and end


select *
from TabA
where charindex(',' + CAST(TabA.ID as nvarchar(20)) + ',', @listOfIDs) > 0
小开

最后我得出的结论是，如果不修改查询的工作方式，我就不能将值存储在变量中。我使用SQL分析器捕获值，然后将它们硬编码到查询中，以查看它是如何工作的。有18个这样的整数数组，有些数组中有超过30个元素。

我认为MS/SQL有必要在语言中引入一些附加的数据类型。数组是非常常见的，我不明白为什么你不能在存储过程中使用它们。

小开

假设变量类似于:

CREATE TYPE [dbo].[IntList] AS TABLE(
[Value] [int] NOT NULL
)

存储过程以这种形式使用它:

ALTER Procedure [dbo].[GetFooByIds]
@Ids [IntList] ReadOnly
As

你可以创建IntList并像这样调用这个过程:

Declare @IDs IntList;
Insert Into @IDs Select Id From dbo.{TableThatHasIds}
Where Id In (111, 222, 333, 444)
Exec [dbo].[GetFooByIds] @IDs

或者如果你自己提供了IntList

DECLARE @listOfIDs dbo.IntList
INSERT INTO @listofIDs VALUES (1),(35),(118);
小开
对于SQL Server 2016+和Azure SQL数据库，添加了STRING_SPLIT函数，这将是这个问题的完美解决方案。以下是文档: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql < / p >

这里有一个例子:

/*List of ids in a comma delimited string
Note: the ') WAITFOR DELAY ''00:00:02''' is a way to verify that your script
doesn't allow for SQL injection*/
DECLARE @listOfIds VARCHAR(MAX) = '1,3,a,10.1,) WAITFOR DELAY ''00:00:02''';


--Make sure the temp table was dropped before trying to create it
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable;


--Create example reference table
CREATE TABLE #MyTable
([Id] INT NOT NULL);


--Populate the reference table
DECLARE @i INT = 1;
WHILE(@i <= 10)
BEGIN
INSERT INTO #MyTable
SELECT @i;


SET @i = @i + 1;
END


/*Find all the values
Note: I silently ignore the values that are not integers*/
SELECT t.[Id]
FROM #MyTable as t
INNER JOIN
(SELECT value as [Id]
FROM STRING_SPLIT(@listOfIds, ',')
WHERE ISNUMERIC(value) = 1 /*Make sure it is numeric*/
AND ROUND(value,0) = value /*Make sure it is an integer*/) as ids
ON t.[Id] = ids.[Id];


--Clean-up
DROP TABLE #MyTable;

查询结果为1,3

小开
在SQL中有一个名为string_split的新函数，如果你使用字符串列表。 参考链接STRING_SPLIT (transact - sql)

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'
SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';

你可以用in来传递这个查询，如下所示:

SELECT *
FROM [dbo].[yourTable]
WHERE (strval IN (SELECT value FROM STRING_SPLIT(@tags, ',') WHERE RTRIM(value) <> ''))
小开

我用这个:

1-在构建的脚本中声明一个临时表变量:

DECLARE @ShiftPeriodList TABLE(id INT NOT NULL);

分配给临时表:

IF (SOME CONDITION)
BEGIN
INSERT INTO @ShiftPeriodList SELECT ShiftId FROM [hr].[tbl_WorkShift]
END
IF (SOME CONDITION2)
BEGIN
INSERT INTO @ShiftPeriodList
SELECT  ws.ShiftId
FROM [hr].[tbl_WorkShift] ws
WHERE ws.WorkShift = 'Weekend(VSD)' OR ws.WorkShift = 'Weekend(SDL)'


END

在WHERE语句中引用表:

INSERT INTO SomeTable WHERE ShiftPeriod IN (SELECT * FROM @ShiftPeriodList)

提交答案