How to pass a variable number of parameters to a SQL Server stored procedure?
You declare the procedure with default parameters and you invoke it with named parameters instead of positional parameters:
CREATE PROCEDURE usp_myProcedure @name varchar(100) = '', @surname varchar(100) = '', @address varchar(100) = ''ASBEGIN...END
to invoke it from T-SQL:
exec usp_myProcedure @name='John', @surname = 'Doe';exec usp_myProcedure @name='Jane', @address = '123 Anystreet';
To invoke it from C#:
SqlCommand cmd = new SqlCommand('usp_MyProcedure', ...);cmd.CommandType = commandtype.StoredProcedure;cmd.Parameters.AddWithValue('@name', 'John');cmd.Parameters.AddWithValue('@surname', 'Doe');
You can use XML types. Here is a sample procedure:
CREATE PROCEDURE udsp_VariableParams(@params XML)ASBEGIN DECLARE @vdoc INT EXEC sp_xml_preparedocument @vdoc OUTPUT, @params SELECT * FROM OPENXML(@vdoc, '/params/param', 1) WITH ([paramname] VARCHAR(40), [paramvalue] VARCHAR(150)) EXEC sp_xml_removedocument @vdoc ENDEXEC udsp_VariableParams '<params> <param paramname="name" paramvalue="value"/> <param paramname="address" paramvalue="value"/> </params>'EXEC udsp_VariableParams '<params> <param paramname="name" paramvalue="value"/> <param paramname="surname" paramvalue="value"/> <param paramname="address" paramvalue="value"/> </params>'
You can set the default values for the parameters at the SP level so that the parameters become optional.
e.g.
CREATE PROCEDURE dbo.Test @param1 int, -- Not an optional @param2 int = 10, --Optional @param3 bit = 0, --Optional @param4 varchar(50) = '', --Optional @param5 nvarchar(100) = null --Optional -- more parameters ..... ASBEGIN -- The SQL goes here...END
Then you can populate the optional parameters at your choice.