如何将多个值传递给存储过程

I have an Excel worksheet with a dropdown data validation box where the user can select from a list of integer values to determine a department or the word "ALL" to get all departments. I'm passing that cell's value to a stored procedure using SQL. In the SQL, I have WHERE Department=@Dept.

我有一个带有下拉数据验证框的Excel工作表,用户可以从中选择整数值列表来确定部门或单词“ALL”以获取所有部门。我正在使用SQL将该单元格的值传递给存储过程。在SQL中,我有WHERE Department = @Dept。

If I'm only going to allow one department choice, this works fine, but I want to also allow for all departments.

如果我只允许一个部门选择,这很好,但我也想允许所有部门。

Are there any ideas on how to make that work? Does it take some sort of trick with the IN operator or something else?

有没有关于如何使这项工作的想法?是否需要使用IN运算符或其他东西的某种技巧?

3 个解决方案

#1

Use a CASE statement, so if it's "ALL" you can pass back the Department field. If it's anything else pass the value you sent in:

使用CASE语句,如果它是“ALL”,您可以传回Department字段。如果还有其他任何内容传递了您发送的值:

Where Department = CASE @input WHEN 'ALL' Then Department ELSE @input END

#2

Two solutions here in the stored script:

存储脚本中的两个解决方案:

put an if statement then you'll have to sets of queries.

放一个if语句然后你将需要查询集。

put an iif/case statement within the criteria.

将iif / case语句放在标准中。

#3

If your user selects "ALL", you can set the paramter to NULL:

如果用户选择“ALL”,则可以将参数设置为NULL:

select * from table1 WHERE Department=isnull(@Dept,Department)

When the value of @Dept is NULL the query will be:

当@Dept的值为NULL时,查询将为:

select * from table1 WHERE Department=Department

just like 1=1.

就像1 = 1。

#1