如何限制 NULL 作为 SQL Server 存储过程的参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/330303/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to restrict NULL as parameter to stored procedure SQL Server?
提问by SharePoint Newbie
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?
为什么不能做这样的事情？
采纳答案by dkretz
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.
存储过程被设计为尽可能轻量级，以尽可能提高数据库性能。参数的数据类型不是为了验证，而是为了使编译器能够为查询优化器提供更好的信息来编译最佳查询计划。为了验证参数的新目的，通过使编译器更加复杂，参数上的 NOT NULL 约束沿着另一条路径前进。因此效率较低且重量较重。
There's a reason stored procedures aren't written as C# functions.
存储过程不作为 C# 函数编写是有原因的。
回答by Unsliced
You could check for its NULL-ness in the sproc and RAISERRORto report the state back to the calling location.
您可以在 sproc 中检查它的 NULL-nessRAISERROR并将状态报告回调用位置。
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
回答by Patrick Fromberg
Your code is correct, sensible and even good practice. You just need to wait for SQL Server 2014which supports this kind of syntax.
你的代码是正确的、明智的，甚至是很好的做法。您只需要等待支持这种语法的SQL Server 2014。
After all, why catch at runtime when you can at compile time?
毕竟，为什么在编译时可以在运行时捕获呢？
See also this Microsoft documentand search for Natively Compiledin there.
另请参阅此 Microsoft 文档并Natively Compiled在其中搜索。
As dkrez says, nullability is not considered part of the data type definition. I still wonder why not.
正如 dkrez 所说，可空性不被视为数据类型定义的一部分。我仍然想知道为什么不。
回答by BigBother
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.".
哦，好吧，我似乎无法编辑@Unsliced 帖子，因为“此编辑偏离了帖子的原始意图。即使是必须进行重大更改的编辑，也应努力维护帖子所有者的目标。”。
So (@crokusek and everyone interested) this is my porposed solution:
所以（@crokusek 和每个感兴趣的人）这是我提出的解决方案：
You could check for its NULL-ness in the sproc and RAISERRORto report the state back to the calling location.
您可以在 sproc 中检查它的 NULL-nessRAISERROR并将状态报告回调用位置。
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
回答by Kakha Middle Or
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.
您可能需要这种语法的原因之一是，当您在 C# 数据集 GUI 向导中使用 sp 时，如果没有空限制，它会创建具有可为空参数的函数。在 sp body 中没有空检查可以帮助它。
