Stored procedures

Question

Stored procedures

anders-731262

Ten Centuries

Points: 1256
More actions
August 3, 2009 at 8:32 pm

#385107

Comments posted to this topic are about the item Stored procedures

Viewing 13 posts - 1 through 12 (of 12 total)

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

Artur Sokhikyan SSC Eights! Points: 900 More actions · Answer 1

nice question

-------------------------
- Name?
- Abu Dalah Sarafi.
- Sex?
- 3 times a week!
- No, no. Male or Female?
- Male, female, sometimes camel...

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 2

damn, really nice question, completely threw me. I was thinking about the fact that "sp_*" procs still need an extra property defined in order to act on objects (tables etc) that belong in the current database.

This sample illustrates what messed me up:

use master

go

create table LocalTable (testcolumn nVarChar(255))

go

INSERT INTO LocalTable SELECT 'This data is in master!'

go

create proc sp_test as

select db_name() AS DBNameFromFunction,

(select count(*) from sysobjects) AS ObjectCountFromSystemView,

(SELECT TOP 1 testcolumn FROM LocalTable) AS DBNameFromLocalObject

go

create database testdb

go

use testdb

go

create table LocalTable (testcolumn nVarChar(255))

go

INSERT INTO LocalTable SELECT 'This data is in testdb!'

go

sp_test

go

use master

go

sp_test

go

use master

go

drop database testdb

drop proc sp_test

drop table LocalTable

go

You expect the "LocalObject" result column to show the results of that table in the current context database, but it does not - it shows you the contents of that table in the master database!

I had a very hard time understanding (or finding any documentation / help on) this behaviour when I first started writing "sp_*" procs, but I haven't looked in a while; I guess it's generally discouraged anyway 🙂

If you really do want to change this behaviour (and see the correct result in the "LocalObject" column returned by the proc above), you need to use the "sp_ms_marksystemobject" stored procedure. Please note, it only makes sense to do this if you know that your proc will only ever be called from databases that will contain the local tables you are referencing.

In my case it makes sense for some management procs that need to work on dozens or hundreds of databases with essentially the same structure - it helps avoid using less-safe and harder-to-read "EXEC()" or "sp_executesql" calls.

(I was tempted to go write my own version of this QotD with the "trick", but felt it infringed a little on Anders' question - maybe something like that is coming anyway)

Hope this helps someone,

Tao

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

Carlo Romagnano SSC-Insane Points: 22672 More actions · Answer 3

Artur Sokhikyan (8/4/2009)
nice question
-------------------------
- Name?
- Abu Dalah Sarafi.
- Sex?
- 3 times a week!
- No, no. Male or Female?
- Male, female, sometimes camel...

Strong!!!!

jjtetzlo SSCrazy Points: 2511 More actions · Answer 4

For those that have html tags inside the question (I'm using Chrome), the question shows up properly in the email... and I posted it below:

use master

go

create database testdb

go

create proc sp_test as

select db_name()

go

sp_test

go

use testdb

go

sp_test

go

use master

go

drop database testdb

drop proc sp_test

go

- Jeff

Cliff Jones SSChampion Points: 10547 More actions · Answer 5

Yes, really nice question that illustrates the perils of naming a stored procedure using the naming convention 'sp_' which is used for system stored procedures.

SanjayAttray SSChampion Points: 13157 More actions · Answer 6

Artur Sokhikyan (8/4/2009)
- Name?
- Abu Dalah Sarafi.
- Sex?
- 3 times a week!
- No, no. Male or Female?
- Male, female, sometimes camel...

ROFL .... Your signature made my day.

SQL DBA.

Cliff Jones SSChampion Points: 10547 More actions · Answer 7

Cliff Jones

SSChampion

Points: 10547

August 4, 2009 at 8:56 am

#1034326

Yes, I am a little worried about Artur. 😎

webrunner SSC-Dedicated Points: 31869 More actions · Answer 8

Thanks, great question. Usually "great question" means one where I don't know enough to get it right, so I was happy to get this one right, and for the right reason!

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

FYI: the BOL link posted after the explanation does not confirm anything said in the explanation.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Well, I know what is supposed to happen so I got my point. But anyone still using SQLServer 2000 should be warned about this - the behaviour is erratic, it may decide that although the proc name begins sp_ it's not going to look for it in master. So it's best always to use exec master..sp_ (in modern SQLServer, exec sys.sp_ - this is explicitly recommended in BOL for 2008) rather than just sp_.

Tom

Bhavesh_Patel SSCrazy Points: 2259 More actions · Answer 11

Good Q.

Learned something new today.

[font="System"]Bhavesh Patel[/font]

http://bhaveshgpatel.wordpress.com/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 12

ColdCoffee

SSC-Dedicated

Points: 39972

February 11, 2010 at 6:58 am

#1117544

Pretty question