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=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?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy