Passing sets and optional parameters to a stored procedure T-SQL - ASP.NET

asp.net, optional-parameters, set, stored-procedures, t-sql

Solution

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

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

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/

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

Problem

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. 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? 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? 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

Original source