Use of SYSNAME data type in/out TSQL procedures

Question

I'm trying to figure out the use of datatype sysname and how the parser implicitly converts and/or recognizes none-quoted-literals. Using the famous function object_id for testing existence, Consider this.

--Sanity test
begin
    if object_id('NotExistingProcedure','P') is not null print N'Exists.';
    else print N'Not exists.';
end
go

--This works.
begin
    declare @ObjectName sysname = 'NotExistingProcedure',
            @ObjectType sysname = 'P';
    if object_id(@ObjectName,@ObjectType) is not null print N'Exists.';
    else print N'Not exists.';
end
go

--This does not.
begin
    declare @ObjectName sysname = NotExistingProcedure,
            @ObjectType sysname = P;
    if object_id(@ObjectName,@ObjectType) is not null print N'Exists.';
    else print N'Not exists.';
end
go

--But this works.
create procedure TestExists
    @ObjectName sysname,
    @ObjectType sysname
as
    if object_id(@Objectname,@ObjectType) is not null print N'Exists.';
    else print N'Not exists.';
go

exec TestExists NotExistingProcedure,P;

Can anyone please explain why using none-quoted-literals to assign variables of type SYSNAME works when passing parameters to a procedure but not outside.

Answer 1

SYSNAME:

The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers. Therefore, it can vary between instances of SQL Server. sysname is functionally the same as nvarchar(128) except that, by default, sysname is NOT NULL. In earlier versions of SQL Server, sysname is defined as varchar(30).

Now in your example that does not work:

begin
    declare @ObjectName sysname = NotExistingProcedure,
            @ObjectType sysname = P;
    if object_id(@ObjectName,@ObjectType) is not null print N'Exists.';
    else print N'Not exists.';
end
go

Error:

Invalid column name 'NotExistingProcedure'.

Invalid column name 'P'.

Based on DECLARE documentation

DECLARE 
{ 
    { @local_variable [AS] data_type  | [ = value ] }
  | { @cursor_variable_name CURSOR }
} [,...n] 

So you could assign value where:

= value

Assigns a value to the variable in-line. The value can be a constant or an expression, but it must either match the variable declaration type or be implicitly convertible to that type.

Then Expressions:

So the parser expects column name, but there is no column name (because it is not a SELECT statement).

That is why you get error:

Invalid column name 'NotExistingProcedure'.

---

Now let's move on to last example that works:

create procedure TestExists
    @ObjectName sysname,
    @ObjectType sysname
as
    if object_id(@Objectname,@ObjectType) is not null print N'Exists.';
    else print N'Not exists.';
go

exec TestExists NotExistingProcedure,P;
go

Based on EXECUTE:

So as you see you could pass value:

value

Is the value of the parameter to pass to the module or pass-through command. If parameter names are not specified, parameter values must be supplied in the order defined in the module.

If the value of a parameter is an object name, character string, or qualified by a database name or schema name, the whole name must be enclosed in single quotation marks. If the value of a parameter is a keyword, the keyword must be enclosed in double quotation marks.

That indicates that is should be closed with '.

If you try to pass with schema:

exec TestExists dbo.NotExistingProcedure, P;
go
-- Incorrect syntax near '.'.

And you need to enclose it with

exec TestExists 'dbo.NotExistingProcedure', P;
go

When it is keyword you could quote it with []:

exec TestExists [from], P;
go

---

To sum up:

1. You cannot pass unquoted name when declaring variable of type SYSNAME.
2. You cannot pass unquoted name with EXEC when it is keyword or contains schema name
3. Could not find documenation why passing unquoted literal works