Stored procedure with variable number of parameters

Question

I have stored procedure where I have to pass parameters, But the problem is I am not sure how many parameters is going to come it can be 1, in next run it can be 5.

cmd.Parameters.Add(new SqlParameter("@id", id) 

Can anyone help how can I pass these variable number of parameters in stored procedure? Thanks

Answer 1

You could pass it in as a comma-separated list, then use a split function, and join against the results.

CREATE FUNCTION dbo.SplitInts (    @List       VARCHAR(MAX),    @Delimiter  CHAR(1) ) RETURNS TABLE AS    RETURN     (        SELECT Item = CONVERT(INT, Item)        FROM        (            SELECT Item = x.i.value('(./text())[1]', 'INT')            FROM            (                SELECT [XML] = CONVERT(XML, '<i>'                      + REPLACE(@List, @Delimiter, '</i><i>')                      + '</i>').query('.')            ) AS a            CROSS APPLY            [XML].nodes('i') AS x(i)        ) AS y        WHERE Item IS NOT NULL    ); 

Now your stored procedure:

CREATE PROCEDURE dbo.doStuff     @List VARCHAR(MAX) AS BEGIN     SET NOCOUNT ON;      SELECT cols FROM dbo.table AS t         INNER JOIN dbo.SplitInts(@List, ',') AS list         ON t.ID = list.Item; END GO 

Then to call it:

EXEC dbo.doStuff @List = '1, 2, 3, ...'; 

You can see some background, other options, and performance comparisons here:

• Split strings the right way – or the next best way
• Splitting Strings : A Follow-Up
• Splitting Strings : Now with less T-SQL
• Comparing string splitting / concatenation methods
• Processing a list of integers : my approach
• Splitting a list of integers : another roundup
• More on splitting lists : custom delimiters, preventing duplicates, and maintaining order
• Removing Duplicates from Strings in SQL Server

On SQL Server 2016 or above, though, you should look at STRING_SPLIT() and STRING_AGG():

• Performance Surprises and Assumptions : STRING_SPLIT()
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #2
• SQL Server v.Next : STRING_AGG() performance
• Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions

Answer 2

Stored procedures support optional parameters. Like C# 4, you can specify a default value using =. For example:

create procedure dbo.doStuff(      @stuffId int = null,       @stuffSubId int = null,       ...) as ... 

For parameters you don't want to pass, either set them to null or don't add them to cmd.Parameters at all. They will have their default value in the stored procedure