存储过程参数"NULL"；或"= NULL" [英] Stored Procedure argument "NULL" or "= NULL"

问题描述

两者之间有什么区别

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语法"

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.

这篇关于存储过程参数"NULL"；或"= NULL"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！