SQL Server Performance Forum – Threads Archive

Parameter NOT NULL

Hi, I’m sure the answer to question I’m about to is NO, but I’d appreciate it if anyone would kindly confirm so. Well, is it possible to declare a Parameter for a Stored Procedure in SQL Server 2000 so that it may not be NULL, for example: CREATE PROC TestProcedure @Parameter INT NOT NULL AS ……

---

The answer is no (there are defaults such as
ALTER PROCEDURE TestProcedure
@Parameter INT = NULL
AS which will take the place of no value being passed.
Usually best to handle it on the client call.

---

Much as I thought really, but I wanted to fully sure there was a way to ensure a Parameter was specified, as opposed to set to NULL. Many thanks for you prompt reassurance.

---

It is probably just extra work for you in this case. If you could use NOT NULL and someone tried, sql server would raise an error. If you check for a null value in the procedure, you can manually raise an error. John

---

Let’s be precise about parameters and default values. The default only comes into play when the parameter is not referenced by the call that executes the stored procedure. So with our sproc … CREATE PROCEDURE TestProcedure (@Parameter INT = 0) AS
……………
… when you call this as:
EXEC TestProcedure
… the default is applied, and the parameter has value 0 at the start of the procedure,
… but when you call this as:
EXEC TestProcedure NULL
… the default is not applied, and the parameter has a null value at the start of the procedure.
So even with a default, you still have to check for NULLs!

---

Adriaan – I agree am aware of your point, and agree with you.
spacemonkey – Thanks for the tip, I will certainly consider implementing the idea when the problem next arises.

---

]]>