Stored Procedure Execution

Question

Stored Procedure Execution

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 1

Very good question, thank you Jason.

Creating a proc with name beginning with sp_ is very evil indeed. While there is a way to invoke the AdventureWorks' version of the proc by schema qualifying it, i.e.

exec Person.sp_ReturnSomething 5;

returns desired

Result

-----------

5

still beginning proc name with sp_ is ill advised for a very good reason.

Oleg

SanjayAttray SSChampion Points: 13157 More actions · Answer 2

Instead of Person.sp_ReturnSomething, create proc with dbo prefix and it returns 5.

I got it wrong as I don't have Adventureworks DB. But, with testing it and getting right would be cheating myself.

Good question. Everyday is a learning day.

SQL DBA.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Thanks - it is a good question, good reminder, and good discussion.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

Reasons I recommend:

A) Never have the first three letters of any stored user procedure be "sp_"

B) Always use three part naming, so you either execute:

Adventureworks.Person.(otherprefix)ReturnSomething

or

Master.dbo.(otherprefix)ReturnSomething

both of which make it explicitly obvious what you're calling.

Ambiguity is a playground of subtle bugs.

sjimmo SSChampion Points: 11139 More actions · Answer 5

Ambiguity is a playground of subtle bugs

I like it.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

UMG Developer SSChampion Points: 13482 More actions · Answer 6

Nadrek (6/9/2010)
B) Always use three part naming, so you either execute:
Adventureworks.Person.(otherprefix)ReturnSomething
or
Master.dbo.(otherprefix)ReturnSomething
both of which make it explicitly obvious what you're calling.

That sounds nice, but it causes problems if for example you need to duplicate the DB to do testing, and don't have a different instance to put it in. Suddenly your SPs are running in the wrong DB, and you would have to go through and refactor all of the code. However, I do agree with using two part naming.

Nadrek SSC-Insane Points: 20039 More actions · Answer 7

UMG Developer (6/9/2010)
Nadrek (6/9/2010)
B) Always use three part naming, so you either execute:
Adventureworks.Person.(otherprefix)ReturnSomething
or
Master.dbo.(otherprefix)ReturnSomething
both of which make it explicitly obvious what you're calling.
That sounds nice, but it causes problems if for example you need to duplicate the DB to do testing, and don't have a different instance to put it in. Suddenly your SPs are running in the wrong DB, and you would have to go through and refactor all of the code. However, I do agree with using two part naming.

True, "best practices" need to be evaluated in relation to any given situation and set of constraints one is under. As the situation and the constraints change, so too do the "best practices" that best fit.

Since I've often dealt with systems that do a reasonable percentage of cross-database, same-instance calls, three part naming across the board makes sense; if you move to differently named database sets on the same instance, you're going to have to do a lot of careful global search and replace anyway.

DLathrop SSCrazy Points: 2018 More actions · Answer 8

Yes, you should definitely avoid using the sp_ prefix. Not just the performance hit, since it always checks the master and resource databases first. Also, the next version of SQL Server could have a new system stored procedure with the same name; you'ld be scratching your head for a long time to figure out why you application was suddenly failing.

However, as a DBA, you can be comfortable using the system stored procedure, even in a develpment database. A programmer can't trip you up by leaving an sp_ stored procedures waiting for you to execute it with SA rights.

David Lathrop
DBA
WA Dept of Health

DLathrop SSCrazy Points: 2018 More actions · Answer 9

Full three part naming helps the query optimizer recognize and reuse queries in the execution plan cache. Queries with unqualified names, including those in stored procedures, have to be fully parsed and processed each time they execute because the name resolution is based on that session's user's default schema.

So if you can use fully qualified names in your environment, it gives much better performance.

If you need a test database, you might consider using another SQL Server instance, a virtual machine, or testing on "developer" workstations.

David Lathrop
DBA
WA Dept of Health

UMG Developer SSChampion Points: 13482 More actions · Answer 10

DLathrop (6/9/2010)
Full three part naming helps the query optimizer recognize and reuse queries in the execution plan cache.

I understand that you need to use two part naming, but I have never heard that you have to use three part naming for the optimizer to recognize and reuse execution plans. Do you have a link to any documentation that supports this?

Cliff Jones SSChampion Points: 10547 More actions · Answer 11

UMG Developer (6/9/2010)
DLathrop (6/9/2010)
Full three part naming helps the query optimizer recognize and reuse queries in the execution plan cache.
I understand that you need to use two part naming, but I have never heard that you have to use three part naming for the optimizer to recognize and reuse execution plans. Do you have a link to any documentation that supports this?

That's news to me to.

DLathrop SSCrazy Points: 2018 More actions · Answer 12

Oops, I checked. The two part schema.object form of name is considered "fully qualified" for plan caching; you don't need to prefix names with the database.

Sorry for sharing my confusion. [In some contexts "fully qualified name" means three or four part names.]

David Lathrop
DBA
WA Dept of Health

itsme.mittal Valued Member Points: 59 More actions · Answer 13

I have used the same code but i have return as 5 as result

But i did not execute whole query in batch i have execute it individual

would you please reply me for it

Aparna-1 SSCrazy Points: 2339 More actions · Answer 14

Good Question. I got it wrong. I did not see that the sp started with sp_.

In my company we follow the standard of not using sp as starting letter for stored procs name and somehow missed to spot this one.

steven.malone Say Hey Kid Points: 688 More actions · Answer 15

The explanation is wrong.

Explanation: "SQL Server uses the sp_ prefix to designate system stored procedures... A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead."

http://msdn.microsoft.com/en-us/library/ms190669.aspx%5B/quote%5D

Just because you create a procedure in master that is named sp_ does not make it a system stored procedure. I am still very angry at MS for taking away the ability to create system functions in 2k5.

In the example the only reason the sproc in master is executed is that the local procedure was in a non-default schema.

This is why people without Adventure Works were getting different results.