将集合和可选参数传递给存储过程T-SQL - ASP.NET

A big task for me. I have a few questions. You guys already helped me so much, but I get one difficulty after another and progressing unbelievably slowly. These questions will better describe my tasks.

对我来说是一项重大任务。我有几个问题。你们这些人已经帮了我很多，但是我遇到了一个接一个的困难，进展令人难以置信的缓慢。这些问题将更好地描述我的任务。

I have an aspx page with the following controls: TextBox, CheckBoxList, DropDownList. They are used to specify search criteria. The records are taken from SQL Server Data Base using a stored procedure.

我有一个带有以下控件的aspx页面：TextBox，CheckBoxList，DropDownList。它们用于指定搜索条件。记录使用存储过程从SQL Server数据库中获取。

0) It's clear how to pass a text from TextBox to search a product by name. No questions here.

0）很清楚如何从TextBox传递文本以按名称搜索产品。这里没有问题。

1) Each CheckBox in CheckBoxList has a value which is actual ID of a Product Group in the database. If more than one checkbox is selected, how to pass the list of IDs to a stored procedure as a set so I could use IN @IdList? What variable types should I use in C# and T-SQL? Is it possible at all?

1）CheckBoxList中的每个CheckBox都有一个值，它是数据库中产品组的实际ID。如果选中了多个复选框，如何将ID列表作为一组传递给存储过程，以便我可以使用IN @IdList？我应该在C＃和T-SQL中使用哪些变量类型？有可能吗？

2) DropDownList's first item is "ALL". When "ALL" is selected, I need to pass something like NULL to the stored procedure. How to make the stored procedure to ignore a parameter if it is NULL?

2）DropDownList的第一项是“ALL”。选择“ALL”时，我需要将类似NULL的内容传递给存储过程。如果存储过程为NULL，如何使存储过程忽略该参数？

I'm exhausted, no ideas are coming any longer, I even tried to search by each criterion separately, then put found IDs into individual arrays, compare them and compose a resulting array with common IDs, but there were numerous problems, I had to use giant static arrays, inefficient memory usage, unable to create dynamic arrays, unable to check intersection of only non-zero elements and so on, so I abandoned that idea...

我已经筋疲力尽了，没有任何想法会再出现，我甚至试图分别搜索每个标准，然后将找到的ID放入单个数组中，比较它们并组成一个带有常见ID的结果数组，但是有很多问题，我不得不使用巨大的静态数组，低效的内存使用，无法创建动态数组，无法检查只有非零元素的交集等等，所以我放弃了这个想法......

CREATE PROCEDURE FilterList
    @ProductName nvarchar(200),
    @ProductGroupID int
AS BEGIN 
SELECT 
    prod_id AS 'ID',
    prod_name AS 'Name'
FROM 
    dbo.Products
WHERE 
    prod_group_id = @ProductGroupID OR
    prod_name = @ProductName
END

2 个解决方案

#1

You can achieve optional parameters in T-SQL stored procedures by having default values equal NULL.

您可以通过使默认值等于NULL来在T-SQL存储过程中实现可选参数。

Example where NULL is used as "ignore this parameter":

将NULL用作“忽略此参数”的示例：

CREATE PROCEDURE [dbo].[dcspFilterEmpList]
    @ProductName nvarchar(200) = null,
    @ProductGroupID int = null
AS BEGIN 

    SELECT
    prod_id AS 'ID',
    prod_name AS 'Name'
    FROM dbo.Products

    WHERE (prod_group_id = @ProductGroupID OR @ProductGroupID IS NULL)
    AND (prod_name = @ProductName OR @ProductName IS NULL)

END

It's perfectly fine to define a stored procedure to take a table-valued parameter in T-SQL. Here is an article on the subject http://sqlwithmanoj.wordpress.com/2012/09/10/passing-multipledynamic-values-to-stored-procedures-functions-part4-by-using-tvp/

定义存储过程以在T-SQL中获取表值参数是完全正确的。这是一篇关于这个主题的文章http://sqlwithmanoj.wordpress.com/2012/09/10/passing-multipledynamic-values-to-stored-procedures-functions-part4-by-using-tvp/

If you need more info on this google for "table-valued parameter"

如果你需要关于这个谷歌的更多信息“表值参数”

Example using multi-valued parameters:

使用多值参数的示例：

CREATE TYPE XTV_ProductNames AS TABLE  --extension, table valued == XTV
( ProductName nvarchar(50))
GO
CREATE TYPE XTV_ProductGroups AS TABLE  --extension, table valued == XTV
( ProductGroupID int))
GO
CREATE PROCEDURE [dbo].[dcspFilterEmpList]
    @TVP1 XTV_ProductNames READONLY
    ,@TVP2 XTV_ProductGroups READONLY
AS BEGIN 
    SELECT
    prod_id AS 'ID',
    prod_name AS 'Name'
    FROM dbo.Products as p
    INNER JOIN @TVP1 as s
        ON p.prod_name = s.ProductName
    UNION
    SELECT
    prod_id AS 'ID',
    prod_name AS 'Name'
    FROM dbo.Products as p
    INNER JOIN @TVP2 as s
        ON p.prod_group_id = s.ProductGroupID
END

#2

Your answer was correct David, but it was a little misleading because the parameter equaling NULL does not make it optional; it just has to be set to any value period. This also creates optional parameters:

你的答案是正确的大卫，但它有点误导，因为参数等于NULL不会使它成为可选;它只需要设置为任何值期间。这也会创建可选参数：

CREATE PROCEDURE [dbo].[dcspFilterEmpList]
    @ProductName nvarchar(200) = 'DefaultProduct',
    @ProductGroupID int = 1
AS

Which could thus be executed without passing any parameters, i.e.:

因此可以在不传递任何参数的情况下执行，即：

EXEC [dbo].[dcspFilterEmpList]

I understand that this wasn't necessarily what Piscovetz was trying to do, but it seems to match more closely the actual topic question shown, and it is an important distinction.

我理解这不一定是Piscovetz试图做的事情，但它似乎与所显示的实际主题问题更加匹配，这是一个重要的区别。

#1