February 8, 2012 at 9:25 am
sathishkumar.k (2/8/2012)
Great question... Is that mean when any object with SP_ prefix used in combination with if (as in this case) would be searched in the current DB first and then will go for master DB? Even if so, won't the if statement given in the question do a second search in the master DB automatically? I am bit confused... :rolleyes:
Hope this example answers your question
Try this code
USE master
GO
IF OBJECT_ID('sp_mytable') IS NOT NULL
DROP TABLE sp_mytable;
GO
CREATE TABLE sp_mytable(col1 tinyint)
GO
INSERT INTO sp_mytable(col1) VALUES (1)
INSERT INTO sp_mytable(col1) VALUES (2)
INSERT INTO sp_mytable(col1) VALUES (3)
GO
-- change to one of your user databases
USE QOD
GO
IF OBJECT_ID('sp_mytable') IS NOT NULL
DROP TABLE sp_mytable;
GO
CREATE TABLE sp_mytable(col1 tinyint)
GO
INSERT INTO sp_mytable(col1) VALUES (10)
INSERT INTO sp_mytable(col1) VALUES (20)
INSERT INTO sp_mytable(col1) VALUES (30)
SELECT * FROM sp_mytable;
--Results:
col1
10
20
30
February 8, 2012 at 10:21 am
bitbucket-25253 (2/8/2012)
sathishkumar.k (2/8/2012)
Great question... Is that mean when any object with SP_ prefix used in combination with if (as in this case) would be searched in the current DB first and then will go for master DB? Even if so, won't the if statement given in the question do a second search in the master DB automatically? I am bit confused... :rolleyes:Hope this example answers your question
Try this code.....
Bitbucket's example shows what WOULD happen. But I think the real point is that, as Microsoft recommends, one should NOT name procedures with an "sp_" prefix. I prefer "usp_" myself.
February 8, 2012 at 10:26 am
john.arnott (2/8/2012)
bitbucket-25253 (2/8/2012)
sathishkumar.k (2/8/2012)
Great question... Is that mean when any object with SP_ prefix used in combination with if (as in this case) would be searched in the current DB first and then will go for master DB? Even if so, won't the if statement given in the question do a second search in the master DB automatically? I am bit confused... :rolleyes:Hope this example answers your question
Try this code.....
Bitbucket's example shows what WOULD happen. But I think the real point is that, as Microsoft recommends, one should NOT name procedures with an "sp_" prefix. I prefer "usp_" myself.
+ 1 for usp or some other prefix following your specific internal good practices
February 8, 2012 at 10:54 am
john.arnott (2/8/2012)
I prefer "usp_" myself.
I prefer not using a prefix at all. I never understand why people think anyone would need a prefix such as "usp_" to be reminded that the identifier following "CREATE PROCEDURE" or "EXEC" is a procedure. As if anything else could be there.
February 8, 2012 at 11:03 am
Hugo Kornelis (2/8/2012)
john.arnott (2/8/2012)
I prefer "usp_" myself.I prefer not using a prefix at all. I never understand why people think anyone would need a prefix such as "usp_" to be reminded that the identifier following "CREATE PROCEDURE" or "EXEC" is a procedure. As if anything else could be there.
Yeah. You're absolutely right. I thought about whether to leave that tag on my post, but then figured what the heck, it's the way I do it. It's just an old habit that sometimes makes sense when I'm adding a procedure to a vendor-supplied db. The prefix allows all our added procedures to sort together.
February 9, 2012 at 5:00 am
Tricky & i think it's better than not knowing at all.:cool:
February 9, 2012 at 6:37 am
BudaCli (2/9/2012)
Tricky & i think it's better than not knowing at all.:cool:
I had hoped that the question would make people aware of the "hidden problems/dangers" that can occur when using the sp_ prefix either to name tables, and/or stored procedures. And hopefully the question has done so.
May 31, 2012 at 8:01 pm
Crazy, absolutely crazy!!!
What do we win with this SQL Server behaviour?????????
Viewing 8 posts - 46 through 52 (of 52 total)
You must be logged in to reply to this topic. Login to reply