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.

Answer 5

Parameter validation is not currently a feature of procedural logic in SQL Server, and NOT NULL is only one possible type of data validation. The CHAR datatype in a table has a length specification. Should that be implemented as well? And how do you handle exceptions? There is an extensive, highly developed and somewhat standards-based methodology for exception handling in table schemas; but not for procedural logic, probably because procedural logic is defined out of relational systems. On the other hand, stored procedures already have an existing mechanism for raising error events, tied into numerous APIs and languages. There is no such support for declarative data type constraints on parameters. The implications of adding it are extensive; especially since it's well-supported, and extensible, to simply add the code:

IF ISNULL(@param) THEN
    raise error ....
END IF

The concept of NULL in the context of a stored procedure isn't even well-defined especially compared to the context of a table or an SQL expression. And it's not Microsoft's definition. The SQL standards groups have spent a lot of years generating a lot of literature establishing the behavior of NULL and the bounds of the definitions for that behavior. And stored procedures isn't one of them.

A stored procedure is designed to be as light-weight as possible to make database performance as efficient as possible. The datatypes of parameters are there not for validation, but to enable the compiler to give the query optimizer better information for compiling the best possible query plan. A NOT NULL constraint on a parameter is headed down a whole nother path by making the compiler more complex for the new purpose of validating arguments. And hence less efficient and heavier.

There's a reason stored procedures aren't written as C# functions.