如何将NULL作为参数限制到存储过程SQL Server?

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?

为什么不可能做这样的事呢?

3 个解决方案

#1

-6

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:

参数验证目前不是SQL Server中过程逻辑的一个特性，not NULL只是一种可能的数据验证类型。表中的CHAR数据类型具有长度规范。这也应该实施吗?如何处理异常呢?对于表模式中的异常处理，有一种广泛的、高度开发的、基于标准的方法;但对于过程逻辑来说不是这样，可能是因为过程逻辑是由关系系统定义的。另一方面，存储过程已经有了一种现有的机制来引发错误事件，这种机制被绑定到许多api和语言中。对于参数上的声明性数据类型约束没有这种支持。增加它的意义是广泛的;特别是由于它得到了良好的支持和扩展，只需添加代码:

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.

在存储过程的上下文中，NULL的概念甚至没有定义，特别是与表或SQL表达式的上下文相比。这不是微软的定义。SQL标准组花费了大量的时间来生成大量的文献，以确定NULL的行为和该行为的定义的界限。存储过程不是其中之一。

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.

存储过程不被编写成c#函数是有原因的。

#2

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

您可以在sproc和RAISERROR中检查它的null值，以将状态报告回调用位置。

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

或

exec dbo.CheckForNull @i = null

#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.

您的代码是正确的、明智的，甚至是良好的实践。您只需等待SQL Server 2014，它支持这种语法。

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

毕竟，为什么要在编译时捕获?

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

请参阅此msdn文档并在其中搜索本地编译。

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

正如dkrez所说，nullabiliy不被认为是数据类型定义的一部分。我仍然想知道为什么不。

#1

-6

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