SQL变量用于保存整数列表。

14 浏览
0 Comments

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上运行报告查询，并使用与报告相同的值呢？

0

3 答案

0 Comments

SQL变量用于保存整数列表的问题是因为需要在存储过程中使用一个可以保存整数列表的变量。解决方法是创建一个用户定义的表类型（User-Defined Table Type），然后在存储过程中使用这个表类型作为参数。

首先，我们可以创建一个名为IntList的表类型，它只有一个列Value，该列的数据类型为int。

然后，存储过程可以使用这个IntList作为只读（ReadOnly）的参数。这意味着存储过程只能读取IntList中的数据，而不能修改它。

我们可以通过以下方式创建IntList并调用存储过程：

1. 如果我们想从另一个表中获取Id列表，我们可以使用以下代码：

DECLARE @IntList AS IntList;
INSERT INTO @IntList 
SELECT Id FROM dbo.{TableThatHasIds}
WHERE Id IN (111, 222, 333, 444);
EXEC [dbo].[GetFooByIds] @IntList;

2. 如果我们想自己提供IntList的值，我们可以使用以下代码：

DECLARE @IntList AS IntList;
INSERT INTO @IntList 
VALUES (1), (35), (118);
EXEC [dbo].[GetFooByIds] @IntList;

通过使用用户定义的表类型作为参数，我们可以在存储过程中传递整数列表，并在存储过程中使用这些值进行操作。这样可以提高代码的可读性和维护性，并且减少了SQL注入的风险。

0
0 Comments

问题的出现原因：SQL-Server没有能够存储整数列表的数据类型，但可以将整数列表存储为字符串。

解决方法：可以将字符串拆分为单独的整数值，并将它们放入一个表中。可以使用动态查询来实现相同的结果。但需要注意，动态SQL存在SQL注入的风险，需要进行适当的清理和消毒。

文章整理如下：

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

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

你可以将字符串拆分为单独的整数值，并将它们放入一个表中。你的存储过程可能已经这样做了。

你也可以使用动态查询来实现相同的结果：

DECLARE @query nvarchar(8000);
SET @query = 'SELECT * FROM TabA WHERE TabA.ID IN (' + @list + ')';
EXECUTE (@query);

注意：我没有对这个查询进行任何清理，所以请注意它容易受到SQL注入的攻击。根据需要进行清理。

谢谢，但是我不能修改一个我不允许修改的查询。

如果有人使用这个方法，请注意如果@list是由用户提供的字符串参数，它可能非常容易受到SQL注入的攻击。根据你的应用程序架构，这可能是一个问题或不是一个问题。

同意，用户需要根据需要自行进行清理和消毒。

我建议在你的答案中添加一个注释来反映这一点。不是每个人都知道这一点，他们只是简单地复制和粘贴解决方案，而不考虑后果。动态SQL非常危险，我像瘟疫一样避免使用它。

另外，我没有给你的答案点赞，但是你能花几分钟时间查看一下我的答案吗？STRING_SPLIT函数非常强大，我认为你会非常喜欢它！

我遇到了错误：将varchar值“68077, 68136, 68143”转换为int数据类型失败。

0
0 Comments

在上述内容中，出现了一个关于SQL变量存储整数列表的问题。问题的原因是在查询中需要使用一个整数列表作为条件进行筛选，但是SQL变量默认只能存储单个值，无法存储整数列表。因此，需要找到一种解决方法来解决这个问题。

解决方法1：

一种解决方法是使用表变量来存储整数列表。表变量可以存储多个行和列的数据，因此可以用来存储整数列表。以下是解决方法的代码示例：

DECLARE @IDs TABLE (ID INT);

INSERT INTO @IDs (ID) VALUES (1), (2), (3);

SELECT *

FROM TabA

WHERE TabA.ID IN (SELECT ID FROM @IDs);

解决方法2：

另一种解决方法是将整数列表转换为字符串，并使用CHARINDEX函数来进行匹配。以下是解决方法的代码示例：

DECLARE @IDs VARCHAR(1000);

SET @IDs = ',1,2,3,'; --在解决方法中需要在开头和结尾加上逗号

SELECT *

FROM TabA

WHERE CHARINDEX(',' + CAST(TabA.ID AS NVARCHAR(20)) + ',', @IDs) > 0;

问题的回答部分提到了另一个相关的问题，即如何处理从查询中返回的多个ID值的情况。可以使用子查询将查询结果存储到变量中，但是需要注意子查询返回多个结果会导致错误。因此，需要在处理这种情况时采取相应的措施。

最后，还提到了另一种解决方案，即将整数列表作为参数传递给查询，但是在SSMS中希望能够创建一个本地变量进行测试。这种情况下，可以将整数列表作为参数传递给查询，而不是使用变量。

通过上述内容，我们了解到了一个关于SQL变量存储整数列表的问题，以及两种解决方法。第一种方法是使用表变量来存储整数列表，第二种方法是将整数列表转换为字符串，并使用CHARINDEX函数进行匹配。此外，还提到了处理从查询中返回多个ID值的情况的注意事项。最后，还提到了一种将整数列表作为参数传递给查询的解决方案。

0