如果 sysname 不能为 null，如何在此 SQL Server 系统存储过程中 sysname = null? [英] How can sysname = null in this SQL Server system stored procedure if sysname cannot be null?

问题描述

我读到 sysname 不能为 null，但是在系统过程 sp_grantdbaccess 的定义中，我看到了对过程参数之一的 null 赋值(如果我没看错的话).这怎么可能?

I read that sysname cannot be null, yet in the definition of the system procedure sp_grantdbaccess, I see the assignment of null to one of the procedure's arguments (if I read this right). How can this be?

ALTER PROCEDURE [sys].[sp_grantdbaccess]
    @loginame   sysname,
    @name_in_db sysname = NULL OUT

推荐答案

"SYSNAME cannot be NULL" 只是不正确.链接问题的答案是正确的，当它说它等效于 NVARCHAR(128) NOT NULL 作为 default -- 然后有效 only 在列定义.比较:

"SYSNAME cannot be NULL" is just not true. The linked question's answer is correct when it says that it's equivalent to NVARCHAR(128) NOT NULL as a default -- and then effectively only in column definitions. Compare:

-- When not specified, columns are NULL
SET ANSI_NULL_DFLT_ON ON  

-- Works
CREATE TABLE T(N NVARCHAR(128)); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

-- When not specified, columns are NOT NULL
SET ANSI_NULL_DFLT_ON OFF

-- Error: can't insert NULL
CREATE TABLE T(N NVARCHAR(128)); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

现在用 SYSNAME 试试同样的方法:

And now try the same with SYSNAME:

-- When not specified, columns are NULL
SET ANSI_NULL_DFLT_ON ON  

-- Error: SYSNAME is NOT NULL, regardless of defaults
CREATE TABLE T(N SYSNAME); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

但这并不意味着SYSNAME不能是NULL，我们要做的就是说它可能是:

But this does not mean SYSNAME cannot be NULL, all we have to do is say it may be:

-- Works
CREATE TABLE T(N SYSNAME NULL); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

在几乎所有使用类型(变量、存储过程参数)的其他上下文中，我们不能指定 NULL 或 NOT NULL 和 NULL 值总是允许的，所以这个 NOT NULL 元数据很少相关.上面的代码使用常规表并不是偶然的:如果你用一个表变量尝试同样的事情，你会发现 ANSI_NULL_DFLT_ON 被忽略，而 NULL 总是列的默认值，如果未指定，则唯一相关的情况是:

In almost all other contexts where a type is used (variables, stored procedure parameters), we cannot specify NULL or NOT NULL and NULL values are always allowed, so this NOT NULL metadata is very rarely relevant. It's no accident that the above code is using regular tables: if you try the same thing with a table variable, you'll find that ANSI_NULL_DFLT_ON is ignored and NULL is always the default for columns, if not specified, so the only relevant cases are:

-- Can't insert NULL
DECLARE @T TABLE (N SYSNAME); INSERT @T DEFAULT VALUES; SELECT * FROM T@
GO

-- OK
DECLARE @T TABLE (N SYSNAME NULL); INSERT @T DEFAULT VALUES; SELECT * FROM @T
GO

这篇关于如果 sysname 不能为 null，如何在此 SQL Server 系统存储过程中 sysname = null?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！