Small Thing Learned Today

You can actually assign default values to stored procedure parameters. I suspect I knew that sometime, but totally forgotter. All I need to do is declare them like this

CREATE PROCEDURE [dbo].[prSP_MyStoredProcWithDefaultParameters]
(@someID int,@someParam1 int = -1,@someParam2 int = -1,@someParam3 int = -1,
@someParam4 int = -1,//...@someParam999 int = -1)

Now if I have less than 999 'someParameters', I can still call the stored procedure. I don't really care how many parameters are in the list, as long as there is no more than 999 of them.

List myParams = new List();
List paramList = new List();
// add some values to the list
foreach (int myInt in paramList)
{ 
 if (paramList.IndexOf(myInt) > 998) 
 {
  break;
 }
 else
 {
  myParams.Add(DbManager.CreateInParameter("someParam" +
 (paramList.IndexOf(myInt) + 1).ToString(), DbType.Int32, myInt)); 
 }
}

Well, I guess I have to take care to assign the really important parameters of course.

I can have default values in SQL Server functions too, but, unfortunately I have to specify the keyword 'default' while calling them.

So, if a function is defined as

CREATE FUNCTION [dbo].[fn_MyFunctionWithDefaultParameters](@someID int,
@someParam1 int = -1,@someParam2 int = -1,
@someParam3 int = -1,@someParam4 int = -1,
//...
@someParam999 int = -1)

I need to call it this way:

dbo.fn_MyFunctionWithDefaultParameters(1, 2, 3, default, default, 
/* snip a few hundred more*/ 
default)

Still quite useful, but I wish I could call it just like this

dbo.fn_MyFunctionWithDefaultParameters(1, 2, 3)

or this

dbo.fn_MyFunctionWithDefaultParameters(1, 2, 3, default, 5, 6)

by Evgeny. Also posted on my website