Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Prefixing procedure names with sp_ is bad practice. Is this true?

Sep 5 2011 2:34AM by Madhivanan   

Yes but partly. Let us explore this with examples

We have a system procedure sp_who that returns the informations about the current connections, users, sessions etc. Let us create a new database named test and create sp_who inside it.

Case 1

Create database test
GO
Use test
GO
create procedure sp_who
as
select 'created in test database'

Now execute this procedure

exec test.dbo.sp_who

As you see from the result, it does not return what is expected but returns the result of what the system procedure sp_who returns.

Case 2

Create a procedure named sp_who_new in master database

Use master
GO
create procedure sp_who_new
as
select 'from master database' as sp_test

Execute this procedure in test database

use test
exec sp_who_new

Eventhough the procedure is not in the test database, SQL Server finds it in master database as the name starts with sp_ and returns the result

Now create the same procedure in test database but change the database name in the select statement

Use test
GO
create procedure sp_who_new
as
select 'from test database' as sp_test

Execute the same procedure in test database and see the result. It actually execute the procedure available in the test database eventhough the same procedure that starts with sp_ is available in master database.

So the conclusion is that only the procedures with sys owner will be executed in master database and local db's procedure will be executed if the owner is not sys.

Tags: sql_server, procedure, sqlserver, tsql, SQL Server, #TSQL, #SQLServer,


Madhivanan
3 · 40% · 12968
4
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Prefixing procedure names with sp_ is bad practice. Is this true?" rated 5 out of 5 by 4 readers
Prefixing procedure names with sp_ is bad practice. Is this true? , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]