June 28, 2011 at 1:33 pm
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.
June 28, 2011 at 1:43 pm
June 28, 2011 at 1:46 pm
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-2=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-2 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?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply