Dynamic number of input parameters for sp 1

[andreis]

Hi, I need to write sp for SQL Server 2005 that fills either 10 or 12 columns of a table, depending on the input variables (10 or 12). How can I make a list of input parameter for sp dynamic (sometimes 10, sometimes 12, depending on a NULL value of one column, say, Price)? Thanks.

 

[RiverGuy]

Set the default of the other 2 parameters to NULL in your stored procedure. If you destination table accepts nulls, then just insert all of the parameters. Otherwise, wrap them in an ISNULL to insert a default value.

 

[andreis]

the problem is that I recieve either 10 or 12 parameters, so calling the sp should look like either:
exec sp_test 1,2,3,4,5,6,7,8,9,10 - or:
exec sp_test 1,2,3,4,5,6,7,8,9,10,11,12.
How can I create sp_test that takes dynamic number of parameters?

 

[jbenson001]

RiverGuy gave you the answer. You create your SP with all 12 parameters. The 2 parameters that you may not get you give a default value of NULL or whatever you want.

@param11 = NULL
@param12 = NULL

Then, as RiverGuy said, just insert all the values, or if the NULLs aren't allowed then use ISNULL.

Insert into...
cols...
values(.., .. , ISNULL(@param11,DefaultVal), ISNULL(@param12,DefaultVal)

 

[RiverGuy]

If parameters 11 & 12 have defaults set to NULL, then the your code exec sp_test 1,2,3,4,5,6,7,8,9,10 will still work fine.

 

[andreis]

thanks a lot, it worked! live and learn...