Why do system stored procs fail with owner included?
The following works in SQL Server:
sp_help MyProc
sp_depends MyTable
But if you include the owner, it fails with a syntax error:
sp_help dbo.MyProc
sp_depends dbo.MyTable
This forces you to put quotes around the parameter for it to work. However, I could do the following without problems:
exec dbo.MyProc
Why inconsistency? Is there a reason?
source to share
SQL Server
allows you to omit single quotes for single word string parameters:
This will output test
:
CREATE PROCEDURE prc_test (@objname NVARCHAR(255))
AS
BEGIN
SELECT @objname
END
prc_test test
however this will fail:
prc_Test Cannot_insert_a_dot.here
Here test
is short form 'test'
(note the single quotes).
SP_HELP
- a stored procedure that expects a parameter VARCHAR
, whereas EXECUTE
expects an object identifier (of which the database name is a valid part)
exec dbo.MyProc
Always enclose string constants in single quotes, and it may be a good idea to enclose object names in parentheses:
EXEC [dbo].[myproc]
source to share
Completion of Quassnoi's answer .
SP_Help
is the SP in the main database that takes an argument NVarchar
for the SP name. If you are using it consistently, you should call
sp_help 'MyProc'
And with the same agreement
sp_help 'dbo.MyProc'
works great.
The point is that when calling sp_help MyProc
SQL Server adds single quotes for you, but that doesn't work when using a dot in the SP name.
source to share