[RESOLVED] Can we Test if a parameter isnt been Given to a Stored Procedure

[killer7k]

Hello,

let's say i have a stored procedure that has a parameter input
i am talking on SQL Server 2000 (No relation about Ado.Net)

can i test if a parameter isnt been given
& show a message ?


Thanks !

[FunkyDexter]

You can give the parm a default value of Null and then test it's value isn't null within the sproc:-

Create Procedure MadeUp
@Parm as varchar(50) = null
As 
Begin
   If @Parm is null then
      Select 'Parm Not Received'
   Else
      Select 'Parm Received OK'
End

That won't distinguish between the parm not being passed and it being explicitely passed as a null though. If you need to distinguish between those two states I guess you could set its default value to something really unlikely to occur and then test for that instead - that's a bit messy though.

[killer7k]

You can give the parm a default value of Null and then test it's value isn't null within the sproc:-

Create Procedure MadeUp
@Parm as varchar(50) = null
As 
Begin
   If @Parm is null then
      Select 'Parm Not Received'
   Else
      Select 'Parm Received OK'
End

That won't distinguish between the parm not being passed and it being explicitely passed as a null though. If you need to distinguish between those two states I guess you could set its default value to something really unlikely to occur and then test for that instead - that's a bit messy though.

Yep m8 Thanks , I made the samething but forgot to give the parm a default value of Null

Thanks !