NULL's causing confusion.

• Shaun Dayaram

  SSC Rookie

  Points: 30

  More actions

  February 7, 2012 at 9:41 am

  #267958

  Hi,

  For my own sanity, I need the following explained to me. It was an issue we picked up in a PROD environment.,

  eg.

  CREATE PROCEDURE dbo.my_proc

  @first INT = null,

  @second int = 2,

  @third int = 3

  AS

  SET NOCOUNT ON;

  SELECT @first, @second, @third;

  GO

  When I execute the sp from SSMS, it works perfectly

  EXECUTE dbo.my_proc

  When it is executed from the Front End Application (VB.Net) the following is passed to SQL

  EXECUTE dbo.my_proc Null,Null,Null

  And this just returns nulls.

  Executed the same come in SSMS and I get the same result, NULLS.

  Why is it when Null's are used the default values are ignored?

  I would have assumed that SQL will see Null's being passed to the sp and will use the default values.:unsure:

  Thanks

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  February 7, 2012 at 9:56 am

  #1443930

  Shaun Dayaram (2/7/2012)

  ---

  Hi,

  For my own sanity, I need the following explained to me. It was an issue we picked up in a PROD environment.,

  eg.

  CREATE PROCEDURE dbo.my_proc

  @first INT = null,

  @second int = 2,

  @third int = 3

  AS

  SET NOCOUNT ON;

  SELECT @first, @second, @third;

  GO

  When I execute the sp from SSMS, it works perfectly

  EXECUTE dbo.my_proc

  When it is executed from the Front End Application (VB.Net) the following is passed to SQL

  EXECUTE dbo.my_proc Null,Null,Null

  And this just returns nulls.

  Executed the same come in SSMS and I get the same result, NULLS.

  Why is it when Null's are used the default values are ignored?

  I would have assumed that SQL will see Null's being passed to the sp and will use the default values.:unsure:

  Thanks

  The defaults are not used because you passed in a null. Adding default values to a stored proc makes the parameter optional. You either need to not pass those parameters or you need to add some additional code inside your proc.

  select @second int = isnull(@second, 2)

  --EDIT: If this wasn't the behavior there would be no way to pass a NULL to a proc and obviously that isn't going to work well.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/