Recognize null in sp  
Author Message
ronniek





PostPosted: Fri Apr 29 11:30:42 CDT 2005 Top

SQL Server Developer >> Recognize null in sp

In my stored proc I need to check whether a empty or NULL value is sent and
check for it in my sp. How can I do this?

David

SQL Server18  
 
 
David





PostPosted: Fri Apr 29 11:30:42 CDT 2005 Top

SQL Server Developer >> Recognize null in sp
BEGIN
/* Do something here */
END

--
David Portas
SQL Server MVP
--

 
 
JJ





PostPosted: Fri Apr 29 11:34:02 CDT 2005 Top

SQL Server Developer >> Recognize null in sp CREATE PROCEDURE MyProc


....................
....................

AS




RAISERROR('Incorrect param(s) values',16,1)

--
Message posted via http://www.sqlmonster.com
 
 
Thomas





PostPosted: Fri Apr 29 11:43:35 CDT 2005 Top

SQL Server Developer >> Recognize null in sp Are you trying to differentiate between a value not passed as opposed to a Null
being passed to a stored procedure? If that is the case, then you need to use a
variant of the "magic values" approach. In the declaration of your procedure,
you need to assigned a default value that presumably could not be passed by the
client code. For example

Create Procedure SaveBalance


...

As


--Value for balance was not passed (i.e. empty)


--Value for balance was specifically passed as Null





HTH


Thomas





> In my stored proc I need to check whether a empty or NULL value is sent and
> check for it in my sp. How can I do this?
>
> David
>


 
 
cbretana





PostPosted: Fri Apr 29 12:22:02 CDT 2005 Top

SQL Server Developer >> Recognize null in sp Because you ask about empty or null, I assume you're talking about an empty
string, or a varchar() paramater... In that case use the NullIF(a, b)
function, which returns a Null if the 2 parameter values (A, B) is



> In my stored proc I need to check whether a empty or NULL value is sent and
> check for it in my sp. How can I do this?
>
> David
>
>
>
 
 
cbretana





PostPosted: Fri Apr 29 13:44:02 CDT 2005 Top

SQL Server Developer >> Recognize null in sp Because you ask about empty or null, I assume you're talking about an empty
string, or a varchar() paramater... In that case use the NullIF(a, b)
function, which returns a Null if the 2 parameter values (A, B) are equal to
one another, and returns the first parameter if they're not...

So




the equivilent of