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

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 !

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

Code:

---

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.

Quote:

---

Originally Posted by FunkyDexter

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

Code:

---

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 !