sql - 查询以处理单个参数以及 "All"情况

Question

我正在创建一个 sql server 存储过程，它将输入作为逗号分隔的 productid 或选项“全部”。当用户传入逗号分隔的产品 ID 时，查询应输出所有产品 ID 的数据。我使用“IN”语句执行此操作。当用户输入选项“ALL”时，查询应显示所有产品的销售数据。有没有其他简单的方法可以让我的查询适用于“ALL”以及逗号分隔的“ProductId's”？我的查询如下:

-- Inputs
Declare @Product varchar(max) = '0022,0033';

-- Code to split string and insert individual productIds into a temp table.
DECLARE @XML AS XML
DECLARE @Delimiter AS CHAR(1) =','
CREATE TABLE #ProductParams  (ProductId INT)
SET @XML = CAST(('<X>'+REPLACE(@Product,@Delimiter ,'</X><X>')+'</X>') AS XML)
INSERT INTO #@ProductParams
SELECT N.value('.', 'INT') AS ID FROM @XML.nodes('X') AS T(N)

-- Sales query for all the products selected.  How can I change the below query so that it displays sales for all products when parameter "All" is passed ?  
select * from SalesTable where ProductId in (select ProductId from #ProductParams)

我不想做以下事情:

If(option is ALL)
run this query
If(option is comma seperated product ids)
run another query.

Answer 1

考虑到当用户选择 ALL 选项时，@Product 将为 NULL

DECLARE @Product VARCHAR(max) = '0022,0033'; -- When user chose ALL then this will be NULL

SELECT * 
FROM   salestable 
WHERE  productid IN (SELECT productid 
                     FROM   #productparams) 
        OR @Product IS NULL 

正如评论中提到的，您可以查看表类型，它应该避免将CSV 解析为过程中的各个行

create type product_list as table
(
productid INT -- Chose relevant datatype 
);

改变程序

Alter procedure proc_name(@prod_list product_list)
as
BEGIN
SELECT * 
FROM   salestable 
WHERE  productid IN (SELECT productid 
                     FROM   @prod_list) 
        OR (select count(1) from @Product) = 0
END

调用

declare @prod_list product_list

Insert into @prod_list(productid) values (0022),(0033)

Exec proc_name @prod_list