Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
 
(untagged)

Input, Output and Input/Output SqlParameter Behavior Explained

0.00/5 (No votes)
1 Nov 2016 1  
In this article, I will explain the sometimes surprising behavior of  the ADO.NET SqlParameter and particularly the effects of ParameterDirection, null value and DBNull.Value on how the parameter is sent to the database.

In this article, I will explain the sometimes surprising behavior of the ADO.NET SqlParameter and particularly the effects of ParameterDirection, null value and DBNull.Value on how the parameter is sent to the database.

The companion VisualStudio 2015 solution contains the database project and unit tests for each scenario outlined below.

I am focusing here specifically on MSSQL stored procedures that define parameters and the ADO.NET SqlCommand of type CommandType.StoredProcedure used to call the stored procedures.

JUMP TO

Input Parameter Without Default on Procedure

Input Parameter With Default on Procedure

Output Parameter Without Default on Procedure

Output Parameter With Default on Procedure

InputOuput Parameter Without Default on Procedure

InputOutput Parameter With Default on Procedure

The following tables describe the various combination of the client-side ADO.NET SqlParameter properties and the corresponding parameter in the SQL Stored Procedure.

The Query Trace row is the query sent to the database as captured by SQL Server Profiler.

All SqlCommands are executed with ExecuteScalar(). See companion GitHub project for exact execution of examples.

Input Parameter Without Default on Procedure

The following 4 examples make use of this procedure:

CREATE PROCEDURE [dbo].[usp_Input_No_Default]
  @param1 varchar(250)
AS
  SELECT @param1;
RETURN 0;
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);

			par.Direction = ParameterDirection.Input;

			par.Value = “Foo”;

			cmd.Parameters.Add(par);
Query Trace: exec usp_Input_No_Default @param1=’Foo’
Results: No surprises, ‘Foo’ is sent to the database and the scalar result value is “Foo”.

Scalar Value: “Foo”
par.Value: “Foo”
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);

			par.Direction = ParameterDirection.Input;

			par.Value = DBNull.Value;

			cmd.Parameters.Add(par);
Query Trace: exec usp_Input_No_Default @param1=NULL
Results: This time, a null valued parameter is sent to the database. The scalar result value is not null, it is DBNull.Value.

Scalar Value: DBNull.Value
par.Value: DBNull.Value
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);

			par.Direction = ParameterDirection.Input;

			par.Value = null;

			cmd.Parameters.Add(par);
Query Trace: exec usp_Input_No_Default @param1=default
Results: SqlException: Procedure or function ‘usp_Input_No_Default’ expects parameter ‘@param1’, which was not supplied.

Since no value is given, ADO.NET applies “default”. Since the procedure defines no default, an error occurs. There must be a value or a default or both.
SqlParameter: No parameter added to SqlCommand.
Query Trace: exec usp_Input_No_Default
Results: SqlException: Procedure or function ‘usp_Input_No_Default’ expects parameter ‘@param1’, which was not supplied.

We get the same exception as providing no Value to the parameter, however the SQL trace shows that the procedure was called without a parameter at all, not even with value “default”.

Input Parameter With Default on Procedure

The following 4 examples make use of this procedure:

CREATE PROCEDURE [dbo].[usp_Input_Has_Default]
  @param1 varchar(50) = 'I''m a default value'
AS
  SELECT @param1;
RETURN 0;
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);

			par.Direction = ParameterDirection.Input;

			par.Value = “Foo”;

			cmd.Parameters.Add(par);
Query Trace: exec usp_Input_Has_Default @param1=’Foo’
Results: Again, no surprises, ‘Foo’ is sent to the database and the scalar result value is “Foo”. The procedure defines a default, but it is not used because a value was provided.

Scalar Value: “Foo”
par.Value: “Foo”
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);

			par.Direction = ParameterDirection.Input;

			par.Value = DBNull.Value;

			cmd.Parameters.Add(par);
Query Trace: exec usp_Input_Has_Default @param1=NULL
Results: Again, a null valued parameter is sent to the database. The scalar result value is not null, it is DBNull.Value. Since null in the database is valid, the procedure honors the request and does not use the default.

Scalar Value: DBNull.Value
par.Value: DBNull.Value
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar);

			par.Direction = ParameterDirection.Input;

			par.Value = null;

			cmd.Parameters.Add(par);
Query Trace: exec usp_Input_Has_Default @param1=default
Results: Since no value is given, ADO.NET applies “default”, and since the procedure has a default this time, the result is:

Scalar Value: “I’m a default value”
par.Value: null
SqlParameter: No parameter added to SqlCommand.
Query Trace: exec usp_Input_Has_Default
Results: Even without supplying a parameter, the procedure applies the default and the scalar value we receive is:

Scalar Value: “I’m a default value”
par.Value: n/a . We did not add a parameter to the command, so we cannot access it. An exception would be thrown if we tried.

Output Parameter Without Default on Procedure

The following 4 examples make use of this procedure. Notice 3 things:

  1. The parameter is defined OUTPUT
  2. We SELECT the parameter (exactly like all the previous examples) before SETting it so we can observe the scalar value returned AND the parameter Value after execution.
  3. We SET the param to a value, after SELECTing, to something other than what we passed in so we can observe the behavior.
CREATE PROCEDURE [dbo].[usp_Output_No_Default]
    @param1 VARCHAR(50) OUTPUT
AS
    SELECT @param1;
    SET @param1 = 'changed by procedure';
RETURN 0
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.Output;

			par.Value = “Foo”;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=NULL

			exec usp_Output_No_Default @param1=@p1 output

			select @p1
Results: This one might surprise you. The trace shows @p1 = NULL even though we gave it a Value of “Foo”.

Scalar Value: DBNull.Value
par.Value: ‘changed by procedure’

It should be clear why par.Value is what it is, OUTPUT parameters can have their value changed during the execution of the procedure and it is reflected back in the client-side code after command execution. But why is the scalar DBNull? That is because the parameter is defined as Output ONLY, any value on it is ignored completely. So when the procedure SELECTs it, it is null, not “Foo”.
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.Output;

			par.Value = DBNull.Value;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=NULL

			exec usp_Output_No_Default @param1=@p1 output

			select @p1
Results: Results exactly the same as when we set the parameter to “Foo”. This is again because values are ignored on Output parameters.

Scalar Value: DBNull.Value
par.Value: ‘changed by procedure’
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.Output;

			par.Value = null;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=NULL

			exec usp_Output_No_Default @param1=@p1 output

			select @p1
Results: Results exactly the same as when we set the parameter to “Foo”. This is again because values are ignored on Output parameters.

Scalar Value: DBNull.Value
par.Value: ‘changed by procedure’
SqlParameter: No parameter added this time.
Query Trace: exec usp_Output_No_Default
Results: SqlException: Procedure or function ‘usp_Output_No_Default’ expects parameter ‘@param1’, which was not supplied.

While we have seen it is pointless to try to set a Value on an input parameter, you still must add the parameter to the Command so that the stored procedure has it to work with (but only if you don’t supply a default as we will see below).

Output Parameter With Default on Procedure

The following 4 examples make use of this procedure. Notice 4 things:

  1. The parameter is defined OUTPUT
  2. The parameter also has a default value of “I’m a default value”
  3. We SELECT the parameter (exactly like all the previous examples) before SETting it so we can observe the scalar value returned AND the parameter Value after execution.
  4. We SET the param to a value, after SELECTing, to something other than what we passed in so we can observe the behavior.
CREATE PROCEDURE [dbo].[usp_Output_Has_Default]
    @param1 varchar(250) = 'I''m a default value' OUTPUT
AS
    SELECT @param1
    SET @param1 = 'changed by procedure';
RETURN 0
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.Output;

			par.Value = “Foo”;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=NULL

			exec usp_Output_Has_Default @param1=@p1 output

			select @p1
Results: Behavior is identical to using an Output parameter without a default value. Your client-side value is ignored.

Scalar Value: DBNull.Value
par.Value: ‘changed by procedure’
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.Output;

			par.Value = DBNull.Value;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=NULL

			exec usp_Output_No_Default @param1=@p1 output

			select @p1
Results: Results exactly the same as when we set the parameter to “Foo”. This is again because values are ignored on Output parameters.

Scalar Value: DBNull.Value
par.Value: ‘changed by procedure’
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.Output;

			par.Value = null;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=NULL

			exec usp_Output_No_Default @param1=@p1 output

			select @p1
Results: Results exactly the same as when we set the parameter to “Foo”. This is again because values are ignored on Output parameters.

But why did an Input parameter with a default use the default when passed null, but an output parameter doesn’t? The reasoning is a mystery to me, but the reason itself lies in the Query Trace. A null valued Input with default becomes “@param=default”, while a null valued Output becomes “@p1=NULL”.

Scalar Value: DBNull.Value
par.Value: ‘changed by procedure’
SqlParameter: No parameter added this time.
Query Trace: exec usp_Output_No_Default
Results: Here we didn’t even add a parameter to the SqlCommand at all and we got back the scalar value that is the parameter’s default in the procedure. As we just saw above, adding a null valued parameter does not trigger the use of the procedure’s default, but omitting the parameter entirely does. Crazy. As we demonstrated, defaults on Output parameters only come into play if you do not send the parameter to the procedure at all.

Scalar Value: “I’m a default value”
par.Value: Not applicable, no parameter added to the SqlCommand so we cannot access its value after command execution. Yes, this basically defeats the purpose of executing a procedure with only output parameters.

InputOutput Parameter Without Default on Procedure

The following 4 examples will again make use of the usp_Output_No_Default we saw earlier:

CREATE PROCEDURE [dbo].[usp_Output_No_Default]
    @param1 varchar(250)
AS
    SELECT @param1;
    SET @param1 = 'changed by procedure';
RETURN 0
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.InputOutput;

			par.Value = “Foo”;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=’Foo’

			exec usp_Output_No_Default @param1=@p1 output

			select @p1
Results: Since this parameter is both input and output, the procedure accepts the initial value of “Foo” and then modifies it where it is reflected back client-side in par.Value.

Scalar Value: “Foo”
par.Value: ‘changed by procedure’
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.InputOutput;

			par.Value = DBNull.Value;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=NULL

			exec usp_Output_No_Default @param1=@p1 output

			select @p1
Results: Again, our input value is accepted and the parameter is modified in the procedure as before.

Scalar Value: DBNull.Value
par.Value: ‘changed by procedure’
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.InputOutput;

			par.Value = null;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=default

			exec usp_Output_No_Default @param1=@p1 output

			select @p1
Results: Exception thrown: ‘System.Data.SqlClient.SqlException’ in System.Data.dll

Additional information: Procedure or function ‘usp_Output_No_Default’ expects parameter ‘@param1’, which was not supplied.

This is interesting because within the trace we can see that the parameter was sent as “@p1=default”, but when it got there was no default to use and an exception occurred. This is the same behavior as an Input only parameter set to null.

You see, there is no strictly OUTPUT parameter in stored procedures. OUTPUT is implicitly both INPUT and OUTPUT. So if you do not provide an Input Value for a parameter without a default your command will fail.
SqlParameter: None added
Query Trace: exec usp_Output_No_Default
Results: An exception of type ‘System.Data.SqlClient.SqlException’ occurred in System.Data.dll but was not handled in user code

Additional information: Procedure or function ‘usp_Output_No_Default’ expects parameter ‘@param1’, which was not supplied.

Same result as a null valued InputOutput parameter. A null input is equivalent to not adding the parameter at all.

InputOutput Parameter With Default on Procedure

The following 4 examples will again make use of the usp_Output_Has_Default we saw earlier:

CREATE PROCEDURE [dbo].[usp_Output_Has_Default]
    @param1 varchar(250) = 'I''m a default value' OUTPUT
AS
    SELECT @param1
    SET @param1 = 'changed by procedure';
RETURN 0
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.InputOutput;

			par.Value = “Foo”;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=’Foo’

			exec usp_Output_Has_Default @param1=@p1 output

			select @p1
Results: Since this parameter is both input and output, the procedure accepts the initial value of “Foo”, ignores the default and then modifies it where it is reflected back client-side in par.Value.

Scalar Value: “Foo”
par.Value: ‘changed by procedure’
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.InputOutput;

			par.Value = DBNull.Value;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=NULL

			exec usp_Output_Has_Default @param1=@p1 output

			select @p1
Results: Again, our input value is accepted and the parameter is modified in the procedure as before.

Scalar Value: DBNull.Value
par.Value: ‘changed by procedure’
SqlParameter: 
SqlParameter par = new SqlParameter(“@param1”, SqlDbType.VarChar, 250);

			par.Direction = ParameterDirection.InputOutput;

			par.Value = null;

			cmd.Parameters.Add(par);
Query Trace: 
declare @p1 varchar(250)

			set @p1=default

			exec usp_Output_Has_Default @param1=@p1 output

			select @p1
Results: This time no exception is thrown because a default is defined in the procedure and it is used in place of the parameter that lacks a value.

Scalar Value: I’m a default value
par.Value: ‘changed by procedure’
SqlParameter: None added
Query Trace: exec usp_Output_No_Default
Results: Again, no exception. However, since we have no client-side SqlParameter, we do not get access to the value set by the procedure.

Scalar Value: I’m a default value
par.Value: n/a

Points to always keep in mind are:

  • If a SqlParameter is defined as Input or InputOutput, and has a null value, the procedure must define a default value.
  • If a stored procedure defines an OUTPUT parameter with a default, the only time that default will be used is if your SqlCommand does not contain that parameter at all.
  • The client-side Value of Output only parameters is discarded so there is no point in setting a value before executing the command.
  • Stored procedures do not have an InputOutput definition like SqlParameters do. All OUTPUT parameters in procedures can be both in and out.
  • If you are inspecting a procedure’s metadata (for instance with sys.parameters), it is impossible to determine if a parameter should be defined as InputOutput in your ADO.NET Command. This comes into play, for example, if you are building a data layer that dynamically creates SqlParameters at run time. If the parameter will have a value client-side then you must choose Input/Output, but this cannot be determined by the metadata.
  • SqlParameter has both a Value and a SqlValue property. What is the difference? Not too much really as you can see from the reference source; when you set the SqlValue it just sets the Value: 
    public object SqlValue {
     get { //omitted for brevity  }
     set { Value = value; }

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here



Permalink

Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Article Copyright 2016 by Bob Crowley
Everything else Copyright © CodeProject, 1999-2024

Web01 2.8:2024-12-08:1