用于保存整数列表的 SQL 变量 [英] SQL variable to hold list of integers

问题描述

我正在尝试调试其他人的 SQL 报告，并将基础报告查询放入 SQL 2012 的查询窗口中.

I'm trying to debug someone else's SQL reports and have placed the underlying reports query into a query windows of SQL 2012.

报告要求的参数之一是整数列表.这是通过多选下拉框在报告上实现的.报告的基础查询在 where 子句中使用这个整数列表，例如

One of the parameters the report asks for is a list of integers. This is achieved on the report through a multi-select drop down box. The report's underlying query uses this integer list in the where clause e.g.

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

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

I don't want to modify the query I'm debugging but I can't figure out how to create a variable on the SQL Server that can hold this type of data to test it.

例如

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

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

There is no datatype that can hold a list of integers, so how can I run the report query on my SQL Server with the same values as the report?

推荐答案

表变量

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 变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！