存储过程参数“NULL"或“= NULL"

2021-11-17 00:00:00 sql arguments default sql-server stored-procedures

有什么区别:

CREATE PROCEDURE [dbo].[MyProcedure] 
                @MyArgument INT NULL

和

CREATE PROCEDURE [dbo].[MyProcedure] 
                @MyArgument INT = NULL

我使用了第一个，它在 SQL Server 2016 中运行良好.但 SQL Server 2012 不接受它.两者都适用于 SQL Server 2016，我现在使用第二个没有问题.但了解其中的差异会很有趣.

I used the first one, and it worked fine in SQL Server 2016. But SQL Server 2012 did not accept it. Both works on SQL Server 2016, and I am using the second one now without problem. But it would be interesting to know the difference.

谢谢！

推荐答案

他们不做同样的事情.第二个为调用者未指定的情况定义默认值.第一个没有.

They don't do the same thing. The second one defines a default value for the case that the caller doesn't specify one. The first one doesn't.

本机编译存储过程的 Transact-SQL 语法"grammar 允许将参数数据类型声明为允许 NULL 或 NOT NULL.这是为 Hekaton(内存优化表)引入的.

The "Transact-SQL Syntax for Natively Compiled Stored Procedures" grammar allows parameter datatypes to be declared as allowing NULL or NOT NULL. This was introduced for Hekaton (memory optimised tables).

虽然在存储过程的 Transact-SQL 语法"中没有记录为支持语法，但它看起来允许 NULL 但在 NOT NULL 和抛出错误.

Though it isn't documented as supported for the grammar in "Transact-SQL Syntax for Stored Procedures" it looks like it allows NULL but balks at NOT NULL and throws an error.

参数 '@MyArgument' 已声明为 NOT NULL.非空参数只支持本地编译的模块，除了用于内联表值函数.

The parameter '@MyArgument' has been declared as NOT NULL. NOT NULL parameters are only supported with natively compiled modules, except for inline table-valued functions.

显式指定 NULL 没有任何价值 - 这是默认且唯一的选项.常规存储过程没有声明性语法来指示参数必须NOT NULL.

There is no value in specifying NULL explicitly - this is the default and only option. There is no declarative syntax for regular stored procs to indicate that parameters must be NOT NULL.

相关文章