What allows SQL Server to trade an object name for a string passed to a system procedure

Question

What causes it to be legal to pass an object name to the system stored procedure sp_helptext?

What mechanism converts the object name to a string?

e.g.

-- works
sp_helptext myproc
sp_helptext [myproc]
sp_helptext [dbo.myproc]
-- and behaves the same as a string
sp_helptext 'myproc'
sp_helptext 'dbo.myproc'

-- does not work
sp_helptext dbo.myproc -- Msg 102, Level 15, State 1, Line 1 incorrect syntax near '.'
-- an additional case that does not work.
sp_helptext [dbo].[myproc] -- Msg 102, Level 15, State 1, Line 1 incorrect syntax

It seems odd that I'm not required to single quote valid proc names, unless it has a . separating schema name and procedure name. I'm looking for an explanation of how it gets auto converted from a quoted name to a string literal to be passed as the value of the parameter.

I don't have a specific issue to resolve; I am simply inquisitive about things that aren't documented.

Answer 1

The first argument to the system stored procedure sp_helptext is:

[@objname= ] 'name'
Is the qualified or nonqualified name of a user-defined, schema-scoped object. Quotation marks are required only if a qualified object is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current database. The object must be in the current database. name is nvarchar(776), with no default.

In addition, the documentation for Delimited Identifiers (Database Engine) states:

Using Identifiers As Parameters in SQL Server
Many system stored procedures, functions, and DBCC statements take object names as parameters. Some of these parameters accept multipart object names, while others accept only single-part names. Whether a single-part or multipart name is expected determines how a parameter is parsed and used internally by SQL Server.

Single-part Parameter Names
If the parameter is a single-part identifier, the name can be specified in the following ways:

• Without quotation marks or delimiters
• Enclosed in single quotation marks
• Enclosed in double quotation marks
• Enclosed in brackets

Multipart Parameter Names
Multipart names are qualified names that include the database or schema name and also the object name. When a multipart name is used as a parameter, SQL Server requires that the complete string that makes up the multipart name be enclosed in a set of single quotation marks.

---

The first argument to sp_helptext accepts both single-part (nonqualified) and multipart (qualified) object names.

If the T-SQL parser interprets the item after sp_helptext as a single-part name (in accordance with the four bullet points above), the resulting name is passed as the (string type) argument value expected by the procedure.

When the parser sees it as a multipart name, the text is required to be surrounded with single quotation marks as stated.

The key feature of a multipart name is a . separator (outside any delimiters).

These examples from the question are successfully interpreted as single-part names:

myproc       - single-part (without quotation marks or delimiters - bullet #1)
[myproc]     - single-part (in brackets - bullet #4)
'myproc'     - single-part (in single quotation marks - bullet #2)
'dbo.myproc' - multipart with the required single quotation marks
[dbo.myproc] - single-part (in brackets - bullet #4)

The last two examples from the question are both parsed as multipart parameter names (due to the exposed . separator). They produce an error because they lack the required enclosing single quotation marks:

dbo.myproc     - multipart without the required single quotation marks
[dbo].[myproc] - multipart without the required single quotation marks

This extra example using double quotation marks is successful:

"dbo.myproc" - single-part (in double quotation marks - bullet point #3)

Note that it is successfully interpreted (for the procedure parameter value) as being a valid single-part name, but the procedure code is able to interpret the (multipart) string it receives flexibly (using PARSENAME and OBJECTID).

As a final point of interest, note that using double quotation marks here does not depend on the setting of QUOTED_IDENTIFIER.