Stored Proc with SP

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Arup Chakraborty » Stored Proc with SP_ prefix

Stored Proc with SP_ prefix

Rate Topic

Display Mode

Topic Options

Author

Message

Ashesh

Posted Monday, August 10, 2009 2:11 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 7:12 AM
Points: 1,417, Visits: 195

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

Post #767670

« Prev Topic | Next Topic »

Permissions