USE tempdbĪs you can see from the error, the first procedure doesn’t work as it returns an error. Neither of them has been marked as a ‘system object’ so let’s see what happens.įirst we’ll change the database context to something other than Master, and then execute the two procedures to see what kind of results we get. So, now I’ve got two procedures in Master, one prefixed with ‘sp_’ and the other with a different prefix ‘usp_’. IF (OBJECT_ID('dbo.usp_MyTest') IS NOT NULL)
IF (OBJECT_ID('dbo.sp_MyTest') IS NOT NULL) However, after a little research I found out that by marking it as a ‘system procedure’, it will allow the procedure to run in the context of the database you are currently in versus running within the Master database, specifically when dealing with system objects.
The discussion was primarily around whether or not you had to mark the stored procedure as a ‘system procedure’. This allowed for direct reference without a three part naming format, IE.
So while at last week’s SQL PASS Summit 2011, during the dinner on Friday night we had a discussion about adding stored procedures to the Master database and being able to refer to it anywhere in your SQL Server instance.
0 kommentar(er)
