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

Question

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

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 

Answer 2

Your code is correct, sensible and even good practice. You just need to wait for SQL Server 2014 which supports this kind of syntax.

After all, why catch at runtime when you can at compile time?

See also this Microsoft document and search for Natively Compiled in there.

As dkrez says, nullability is not considered part of the data type definition. I still wonder why not.

Answer 3

Oh well, it seems I cannot edit @Unsliced post because "This edit deviates from the original intent of the post. Even edits that must make drastic changes should strive to preserve the goals of the post's owner.".

So (@crokusek and everyone interested) this is my porposed solution:

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 
  @name sysname = 'parameter',
  @value sql_variant
as
begin
  if @value is null
    raiserror('The value for %s should not be null', 16, 1, @name) -- with log
end
GO

Then call:

exec dbo.CheckForNull @name 'whateverParamName', @value = 1

or

exec dbo.CheckForNull @value = null 

Answer 4

One reason why you may need such syntax is that, when you use sp in C# dataset GUI wizard, it creates function with nullable parameters if there is no null restriction. No null check in sp body helps it.