How to restrict NULL as parameter to stored procedure SQL Server?

Question

Hi,

Is it possible to create a stored procedure as

CREATE PROCEDURE Dummy @ID INT NOT NULL AS BEGIN END

Why is it not possible to do something like this?

Answer 1

Ummm ... why should it be possible? NOT NULL is not part of the datatype. You can't say "BETWEEN 5 AND 7" either.

You'll need to put logic into the SP to trap any null, and presumably raise an error.

Answer 2

You could check for its NULL-ness in the sproc and RAISERROR to report the state back to the calling location.

CREATE   proc dbo.CheckForNull @i int 
as
begin
  if @i is null 
    raiserror('The value for @i should not be null', 15, 1) -- with log 

end
GO

Then call:

exec dbo.CheckForNull @i = 1

or

exec dbo.CheckForNull @i = null