Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Proc with SP_ prefix Expand / Collapse
Author
Message
Posted Monday, August 10, 2009 2:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 31, 2013 4:24 AM
Points: 1,417, Visits: 197
I completely agree with the fact that stored proc with the prefix "sp_" is reserved for system stored procs. However, the 2nd part is not correct.
If a stored procedure uses same name in both the user database and a system database, the stored procedure in the user database will never get executed.

To verify this I've executed the below code, which suggest irrespective of the prefix, it’s going to execute the proc which has been referred.
T-SQL Code
use master
drop proc dbo.sp_test
go
create proc dbo.sp_test
as
print 'SP from Master DB'
go
use AdventureWorks
go
drop proc dbo.sp_test
go
create proc dbo.sp_test
as
print 'SP from AdvWork DB'
go

exec dbo.sp_test

Result

SP from AdvWork DB

Thanks,
Ashesh



Thanks,
Ashesh


Post #767670
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse