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?

Giveandtake

Answer 1

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.

Eudo

"why should it be possible" - Ummm... because it's a very commonly-used shortcut in the CREATE TABLE statement, so not having it for CREATE PROCEDURE is inconsistent? How hard would it be for SQL Server to check the parameter for null in the exact same way it does for table columns? – Anaesthetize

'NOT NULL' is part of the datatype. Think of it as 'nullable' versus 'non-nullable'. One is a type that contains all the 'standard' values, and also includes a special NULL case value. The other contains only the 'standard' values. The two types are based on the same underlying values, but one supplies a special case. – Menell

NOT NULL is not part of the data type. It's part of the column definition for a table. NULL is typeless. Thee is no such concept as NULL INTEGER or NULL VARCHAR. Yes, it's part of the DDL for asserting a column in a table. So is DEFAULT, and so are index and foreign key definitions. Are we saying that stored procedure parameters are to be considered orthogonal with table columns? – Eudo

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 

Suffix

Hoping someone will update this answer to accept an optional parameter name for the error message, accept all common types (bigint, varchar, ..., and raise only if all of them are null). – Ferocious

@Ferocious if still interested I made a new post down below. Cheers. – Tophus

I don't have time to update this right now but it should be noted that as of SQL 2014 raiserror is not supported in stored procedures, but is replaced by THROW which was introduced in 2012 – Gomorrah

Since SQL Server 2005 we should use THROW instead of RAISERROR, though in a PROCEDURE I think a non-zero RETURN code might be more appropriate than a THROW depending on how exceptional the error is. – Kenric

Answer 3

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.

Lottery

Not available on 2014, just confirmed. – Oldtime

@SajjanSarkar, your comment is a bit short. Did you try the syntax for Natively Compiled STP's? – Lottery

Answer 4

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 

Tophus

I don't have time to update this right now but it should be noted that as of SQL 2014 raiserror is not supported in stored procedures, but is replaced by THROW which was introduced in 2012 – Gomorrah

@TheHitchenator yeah, you're right, but I think original answer dates back to 2008 and I cannot test it right now. Change should be trivial anyway. – Tophus

@BigBrother yep, I'm aware it predates the change, just figured since I found this post others are likely to so worth having the info at the very least in a comment. – Gomorrah

@TheHitchenator I completely agree – Tophus

@TheHitchenator Can you point me to a document for this? I'm using raiseerror in a stored proc (SQL server version 12.0) and not seeing any issues. learn.microsoft.com/en-us/sql/t-sql/language-elements/… suggests only that it does honor SET XACT_ABORT. – Siliqua

Answer 5

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.

Juster