Skip to main content
Database Administrators

Return to Question

move string versions below object versions and explain that they are there to describe behavior
Source Link
JJS
JJS
  • 721
  • 1
  • 5
  • 17

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'
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.

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 'myproc'
sp_helptext 'dbo.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.

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.

Tweeted twitter.com/StackDBAs/status/808097217013809152
Post Unlocked by Paul White♦
Notice removed Wiki Answer by Paul White♦
Post Locked by Paul White♦
Notice added Wiki Answer by Paul White♦
Mod Moved Comments To Chat
Formatting; improved title and tags; added some information from comments
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

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

whatWhat causes it to be legal for one to pass an object name to the system stored procedure sp_helptext.?

What mechanism converts the object name to a string?

e.g.


sp_helptext myproc
sp_helptext [myproc]
sp_helptext 'myproc'
sp_helptext 'dbo.myproc'
sp_helptext [dbo.myproc]

-- does not work
sp_helptext dbo.myproc -- Msg 102, Level 15, State 1, Line 1 incorrect syntax near '.'
2016-11-21 discovered an additional case that does not work.
sp_helptext [dbo].[myproc] -- Msg 102, Level 15, State 1, Line 1 incorrect syntax
-- works
sp_helptext myproc
sp_helptext [myproc]
sp_helptext 'myproc'
sp_helptext 'dbo.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.

what allows ssms to trade an object name for a string passed to a system procedure

what causes it to be legal for one to pass an object name to the system stored procedure sp_helptext. What mechanism converts the object name to a string?

e.g.


sp_helptext myproc
sp_helptext [myproc]
sp_helptext 'myproc'
sp_helptext 'dbo.myproc'
sp_helptext [dbo.myproc]

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

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

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 'myproc'
sp_helptext 'dbo.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.

added additional case that does not work.
Source Link
JJS
JJS
  • 721
  • 1
  • 5
  • 17

what causes it to be legal for one to pass an object name to the system stored procedure sp_helptext. What mechanism converts the object name to a string?

e.g.

sp_helptext myproc
sp_helptext [myproc]
sp_helptext 'myproc'
sp_helptext 'dbo.myproc'
sp_helptext [dbo.myproc]

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

what causes it to be legal for one to pass an object name to the system stored procedure sp_helptext. What mechanism converts the object name to a string?

e.g.

sp_helptext myproc
sp_helptext [myproc]
sp_helptext 'myproc'
sp_helptext 'dbo.myproc'
sp_helptext [dbo.myproc]

-- does not work
sp_helptext dbo.myproc -- Msg 102, Level 15, State 1, Line 2 incorrect syntax near '.'

what causes it to be legal for one to pass an object name to the system stored procedure sp_helptext. What mechanism converts the object name to a string?

e.g.

sp_helptext myproc
sp_helptext [myproc]
sp_helptext 'myproc'
sp_helptext 'dbo.myproc'
sp_helptext [dbo.myproc]

-- does not work
sp_helptext dbo.myproc -- Msg 102, Level 15, State 1, Line 1 incorrect syntax near '.'
2016-11-21 discovered an additional case that does not work.
sp_helptext [dbo].[myproc] -- Msg 102, Level 15, State 1, Line 1 incorrect syntax
Source Link
JJS
JJS
  • 721
  • 1
  • 5
  • 17