Know Dot Net Article

ADO.NET- Supply Default Values for your Parameters

by William Ryan

Few people are as die hard about using Stored Procedures as I am.  I love everything about them, period.  However, one common mistake that people make when moving to ADO.NET is forgetting to supply a value for a given parameter.  Hence, when the command is executed, an exception is thrown indicating that a parameter value is missing.  Remember that when you are using T-SQL in Query Analyzer for instance, you can call your procs passing parameters by position or name.  You can effectively do the same in ADO.NET by not specifying a parameter name, but that's sloppy and error prone.

Anyway, depedning on your proc, it may be useful to supply default values for your procs. Beware though that if you do this, you may inadvertantly introduce subtle bugs in your code because you won't get the warning message.

Here's all there is to it:

CREATE PROCEDURE  usp_CalculateValue

     @p_FirstValue       INT      =  0
    ,@p_SecondValue INT      =  0
    ,@p_TaxRate         DEC(4) = 50
AS

SELECT (@p_FirstValue + @p_SecondValue) * @p_TaxRate

GO

To call it, any of these invocations will work.

SqlConnection cn = new SqlConnection("ConnectString"0;
SqlCommand cmd = new SqlCommand("usp_CalcluateValue", cn);
cmd.CommandType = CommandType.StoredProcedure;
if(cn.State <> ConnectionState.Open){cn.Open();}

int i = cmd.ExecuteScalar();  //returns 0

SqlConnection cn = new SqlConnection("ConnectString"0;
SqlCommand cmd = new SqlCommand("usp_CalcluateValue", cn);
cmd.CommandType = CommandType.StoredProcedure;
if(cn.State <> ConnectionState.Open){cn.Open();}
cmd.Parameters.Add("@p_FirstValue", 5);

int i = cmd.ExecuteScalar();  //returns 250

SqlConnection cn = new SqlConnection("ConnectString"0;
SqlCommand cmd = new SqlCommand("usp_CalcluateValue", cn);
cmd.CommandType = CommandType.StoredProcedure;
if(cn.State <> ConnectionState.Open){cn.Open();}
cmd.Parameters.Add("@p_SecondValue", 5)
int i = cmd.ExecuteScalar();  //returns 250

SqlConnection cn = new SqlConnection("ConnectString"0;
SqlCommand cmd = new SqlCommand("usp_CalcluateValue", cn);
cmd.CommandType = CommandType.StoredProcedure;
if(cn.State <> ConnectionState.Open){cn.Open();}
cmd.Parameters.Add(5);

int i = cmd.ExecuteScalar();  //returns 250

SqlConnection cn = new SqlConnection("ConnectString"0;
SqlCommand cmd = new SqlCommand("usp_CalcluateValue", cn);
cmd.CommandType = CommandType.StoredProcedure;
if(cn.State <> ConnectionState.Open){cn.Open();}
cmd.Parameters.Add(5);
cmd.Parameters.Add(5)

int i = cmd.ExecuteScalar();  //returns 500

SqlConnection cn = new SqlConnection("ConnectString"0;
SqlCommand cmd = new SqlCommand("usp_CalcluateValue", cn);
cmd.CommandType = CommandType.StoredProcedure;
if(cn.State <> ConnectionState.Open){cn.Open();}
cmd.Parameters.Add("@p_FirstValue", 5);
cmd.Parameters.Add("@p_SecondValue", 5)

int i = cmd.ExecuteScalar();  //returns 500

As a personal preference, I'd highly recommend against not naming your parameters when you are calling it from ADO.NET (on the other hand, naming them in Query Analyzer is a pain in the butt) because it's easy to get confused.  Moreover, you probably want to be as precise as you can be for performance reasons.  However, if you have a need for Default parameters in ADO.NET, you now know how to do it.