Null values to Int parameter in the Stored Procedure

• Lucky9

  Default port

  Points: 1470

  More actions

  June 28, 2011 at 1:33 pm

  #240685

  Hi

  I have a stored procedure and i do have 2 parameters

  Create procedure test

  ( @a nvarchar(100) = NULL,

  @b-2 int )

  As

  Begin

  Select * from Table

  Where (col1 = @a or @a is Null)

  and (col2 = @b-2)

  End

  so while application calling the stored procedure it may or may not pass the @b-2 parameter.

  so how can i set to empty/null to parameter @b-2

  could someone help me with the stored procedure.

• Jayanth_Kurup

  SSC-Insane

  Points: 22967

  More actions

  June 28, 2011 at 1:40 pm

  #1345460

  defaulting parameter is the same regardless of datatype

  Were you facing any particular issue when you defaulted @b-2 to null ?

  Jayanth Kurup[/url]

• Lucky9

  Default port

  Points: 1470

  More actions

  June 28, 2011 at 1:43 pm

  #1345462

  So if i change the stored procedure as below. will it work even though if someone doesn't pass the parameter @b-2

  Create procedure test

  ( @a nvarchar(100) = NULL,

  @b-2 int = NULL )

  As

  Begin

  Select * from Table

  Where (col1 = @a or @a is Null)

  and (col2 = @b-2 or @b-2 is Null)

  End

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  June 28, 2011 at 1:46 pm

  #1345467

  As currently defined your procedure actually allows @b-2 to be null - what it doesn't do is it doesn't allow you to skip passing the parameter. In other word - you COULD pass in a null as it stands now simply with calling the stored proc as

  Exec test @b=null

  or

  Exec test null, null

  If you want to have the option to not pass the parameter at all, use a similar notation as you did for @a to set a default value on the parameter (if that's appropriate in the calling context).

  i.e.

  Create procedure test

  ( @a nvarchar(100) = NULL,

  @b int = NULL)

  etc...

  That may not always make sense, so think through what would happen if you use that default value.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?