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.
SE Anon or Wiki user (imported from SE)
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`]. : https://msdn.microsoft.com/en-us/library/ms176112.aspx : https://technet.microsoft.com/en-us/library/ms176027.aspx#Anchor_2 : https://msdn.microsoft.com/en-us/library/ms188006.aspx : https://msdn.microsoft.com/en-us/library/ms190328.aspx : https://msdn.microsoft.com/en-us/library/ms174393.aspx