Передача наборов и необязательных параметров в хранимую процедуру T-SQL - ASP.NET

Большая задача для меня. У меня есть несколько вопросов. Вы, ребята, уже очень помогли мне, но я получаю одну трудность за другой и прогрессирующую невероятно медленно. Эти вопросы лучше описывают мои задачи.

У меня есть страница aspx со следующими элементами управления: TextBox, CheckBoxList, DropDownList. Они используются для указания критериев поиска. Записи берутся из базы данных SQL Server с помощью хранимой процедуры.

0) Это ясно, как передать текст из TextBox для поиска продукта по названию. Здесь нет вопросов.

1) Каждый CheckBox в CheckBoxList имеет значение, которое является фактическим идентификатором группы продуктов в базе данных. Если выбрано более одного флажка, как передать список идентификаторов в хранимую процедуру как набор, чтобы я мог использовать IN @IdList? Какие типы переменных следует использовать в С# и T-SQL? Возможно ли вообще?

2) DropDownList первый элемент - "ВСЕ". Когда выбрано "ALL", мне нужно передать что-то вроде NULL в хранимую процедуру. Как заставить хранимую процедуру игнорировать параметр, если он NULL?

Я измучен, идеи больше не появляются, я даже попытался выполнить поиск по каждому критерию отдельно, а затем поместить найденные идентификаторы в отдельные массивы, сравнить их и составить результирующий массив с общими идентификаторами, но было множество проблем, Я должен был использовать гигантские статические массивы, неэффективное использование памяти, не смог создать динамические массивы, не смог проверить пересечение только ненулевых элементов и т.д., Поэтому я отказался от этой идеи...

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

Ответы

Ответ 1

Вы можете получить дополнительные параметры в хранимых процедурах T-SQL, используя значения по умолчанию равные NULL.

Пример, где 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

Совершенно нормально определять хранимую процедуру для принятия параметра таблицы в T-SQL. Вот статья на эту тему http://sqlwithmanoj.wordpress.com/2012/09/10/passing-multipledynamic-values-to-stored-procedures-functions-part4-by-using-tvp/

Если вам нужна дополнительная информация об этом google для параметра "table-valued parameter"

Пример использования многозначных параметров:

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

Ваш ответ был верным Дэвидом, но он немного вводил в заблуждение, потому что параметр, равный NULL, не делает его необязательным; он просто должен быть установлен в любой период значений. Это также создает дополнительные параметры:

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

Таким образом, это может быть выполнено без передачи каких-либо параметров, то есть:

EXEC [dbo].[dcspFilterEmpList]

Я понимаю, что это не совсем то, что пытался сделать Писковец, но, похоже, он более точно соответствует фактическому вопросу о теме, и это важное различие.