2

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
youcantryreachingme
  • 1,065
  • 11
  • 17
  • 1
    If you read the complete answer at the link you provided you will see that for `SYSNAME` `NOT NULL` is the default, but it can also be overridden, which is effectively what is happening here. – RBarryYoung Sep 11 '18 at 22:47
  • 2
    The default nullability of a type is ignored in some circumstances, and stored procedure parameters are one of those. Variables are another: `DECLARE @n SYSNAME` will work just fine, and it always produces a variable with an initial value of `NULL`. For stored procedure parameters, what matters is whether or not the parameter was supplied, not its value -- `NULL` is always permitted. `sp_grantdbaccess @loginame=NULL` produces an error only because `sp_grantdbaccess` explicitly checks (by way of `sp_validname`) -- the engine does not prevent the `NULL` from being assigned. – Jeroen Mostert Sep 11 '18 at 22:55
  • @RBarryYoung - the accepted answer quotes the official docs as saying "sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable" which conflicts with the later comment that null is simply the default value, which is why I asked the question - I'd have thought the official docs take precedence over the answer comment. – youcantryreachingme Sep 12 '18 at 04:54
  • Thanks @JeroenMostert - From your explanation I deduce the quoted documentation probably means that a column of data type sysname may not be persisted with a null value - as opposed to meaning that a variable of that type may not be null within the scope of a procedure, etc. – youcantryreachingme Sep 12 '18 at 05:01
  • 1
    That's also not true. I'd better write up an answer... – Jeroen Mostert Sep 12 '18 at 08:21

1 Answers1

5

"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

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

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

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
Jeroen Mostert
  • 27,176
  • 2
  • 52
  • 85
  • Thank you - the answers have taught me to think about the different contexts of column definitions vs variables, and that the MS remark is not strictly true that reads "sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable"; it should probably have "by default" appended. – youcantryreachingme Sep 13 '18 at 05:46