如何限制 NULL 作为 SQL Server 存储过程的参数? [英] How to restrict NULL as parameter to stored procedure 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?

推荐答案

参数验证目前不是 SQL Server 中过程逻辑的功能，NOT NULL 只是一种可能的数据验证类型.表中的 CHAR 数据类型具有长度规范.这也应该实施吗?以及如何处理异常?对于表模式中的异常处理，有一种广泛的、高度发达的并且有些基于标准的方法；但不适用于过程逻辑，可能是因为过程逻辑是在关系系统之外定义的.另一方面，存储过程已经具有用于引发错误事件的现有机制，并绑定到众多 API 和语言中.对参数的声明性数据类型约束没有这样的支持.添加它的含义是广泛的.特别是因为它得到了很好的支持和扩展，只需添加代码:

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

存储过程上下文中 NULL 的概念甚至没有明确定义，尤其是与表或 SQL 表达式的上下文相比.这不是微软的定义.SQL 标准组花了很多年时间产生了大量文献来建立 NULL 的行为和该行为的定义范围.存储过程不是其中之一.

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.

存储过程被设计为尽可能轻量级，以尽可能提高数据库性能.参数的数据类型不是为了验证，而是为了使编译器能够为查询优化器提供更好的信息来编译最佳查询计划.为了验证参数的新目的，通过使编译器更加复杂，参数上的 NOT 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.

存储过程没有写成 C# 函数是有原因的.

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

这篇关于如何限制 NULL 作为 SQL Server 存储过程的参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！