sql-server add tag
JJS (imported from SE)
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.
Top Answer
SE Anon or Wiki user (imported from SE)
The first argument to the system stored procedure [`sp_helptext`][1] 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][2]**  
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`][3] and [`OBJECTID`][4]).

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

  [1]: https://msdn.microsoft.com/en-us/library/ms176112.aspx
  [2]: https://technet.microsoft.com/en-us/library/ms176027.aspx#Anchor_2
  [3]: https://msdn.microsoft.com/en-us/library/ms188006.aspx
  [4]: https://msdn.microsoft.com/en-us/library/ms190328.aspx
  [5]: https://msdn.microsoft.com/en-us/library/ms174393.aspx

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.