Remember this if you want to use sp_

Erik Darling, of Erik Darling Data (blog | Twitter) recently posted a very interesting video (my YouTube playlist is all Erik Darling Data videos and Honest Trailers). In this video he demonstrates an easily repeatable issue about naming weirdness and the CREATE OR ALTER PROCEDURE syntax, with stored procedures starting with sp_. The video is 141 seconds long, so go watch it. I’ll wait.

Cool. As many of my readers know — because you’re intelligent and attractive in equal measure — Microsoft does not want us using sp_ for stored procedure prefixes for general use, because it’s kind of reserved:

Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name.

Of course our community likes to release super amazing stored procedures like sp_WhoIsActive and sp_Blitz and sp_HumanEvents, so this isn’t a hard and fast rule.

Create or alter

You can quite easily create a stored procedure with the prefix, both in the master database and any user databases you have, using CREATE PROCEDURE. However what Erik shows is that if you already have a stored procedure with a name that has the sp_ prefix in the master database, and then use CREATE OR ALTER syntax to create a stored procedure in a user database with the same name, you’ll get an “Invalid object name” error in the user database:

Msg 208, Level 16, State 6, Procedure sp_DoYouEvenLiftBro, Line 1 [Batch Start Line 18]
Invalid object name 'dbo.sp_DoYouEvenLiftBro'.

So far, so good. I’ve summarised in 200 words what Erik showed in his video. Then again, this is an edge case and not a typical situation someone would find themselves in, because:

• You shouldn’t be creating regular stored procedure names with the sp_ prefix in the first place;
• You shouldn’t have objects with the same name in the master database and a user database because that’s just confusing.

Except…

Except this is not entirely true. If you run the CREATE OR ALTER statement on the user database a second time, it will execute successfully. The “OR ALTER” part works just fine. It seems to get hung up with the “CREATE” part, but not CREATE on its own.

Stay with me folks, because it’s about to get weirder.

System objects

I happen to know of an undocumented system stored procedure called sys.sp_MS_marksystemobject, which can mark a database object (by convention in the master database) as a system object, and therefore accessible from any user context without needing the three-part name. So what if I mark the master database version of the stored procedure as a system object? I’m so glad you asked, invisible question-asker. The answer is, there’s no error at all in the user database. If the stored procedure with the same name already exists in the master database, but is marked as a system object, then you can quite easily create a stored procedure with that name in a user database, using CREATE OR ALTER.

Summarise this for me

1. Don’t create stored procedures with an sp_ prefix.
2. If you do, don’t create stored procedures with an sp_ prefix, with the same name, in the master and user databases.
3. If you do, don’t use CREATE OR ALTER to create the user database version of the stored procedure.
4. If you do, you have to mark the master database version as a system object first or it will fail.
5. But only on the CREATE part, not the ALTER part, because that works fine.

Got that? Good.

Script to play with

USE master;
GO

DROP PROCEDURE IF EXISTS dbo.sp_DoYouEvenLiftBro
GO
CREATE OR ALTER PROCEDURE dbo.sp_DoYouEvenLiftBro
--CREATE PROCEDURE dbo.sp_DoYouEvenLiftBro
AS
SELECT 'If you''re not afraid of getting schvitzy' AS GiveYourLegsAnIncrediblePump
GO

--EXEC sys.sp_MS_marksystemobject 'sp_DoYouEvenLiftBro'
--GO

USE Test;
GO

DROP PROCEDURE IF EXISTS dbo.sp_DoYouEvenLiftBro
GO
CREATE OR ALTER PROCEDURE dbo.sp_DoYouEvenLiftBro
--CREATE PROCEDURE dbo.sp_DoYouEvenLiftBro
AS
SELECT 'If you''re not afraid of getting schvitzy' AS GiveYourLegsAnIncrediblePump
GO

--EXEC sys.sp_MS_marksystemobject 'sp_DoYouEvenLiftBro'
--GO

Leave your thoughts in the comments below.

Photo by Nathan Dumlao on Unsplash.