Fog Creek Software
Discussion Board




MS SQL 2000 Stored Procedure

I've created a sort of 'utility' stored procedure that I would like to be executable using any DB in query analyzer.

Is there a simple way I can make the procedure available in the context of any database on the server?

I want to avoid putting a separate copy of the procedure in each database.

One way I thought about doing this is to make the database name a parameter for the procedure. However, I don't like that this would require typing the desired database name every time the procedure is to be run.

Chance Govar
Wednesday, August 11, 2004

Add your stored procedure to the master DB and prefix it with sp_

All DBs can now access your proc

Chris Peacock
Wednesday, August 11, 2004

I knew it would be something simple but found it amazingly elusive in the help files.

Thank you very much.

Chance Govar
Wednesday, August 11, 2004

On a tangentially-related note, only prefix a stored procedure with "sp_" if you're going to put it in the master database:

http://www.winnetmag.com/SQLServer/Article/ArticleID/23011/23011.html

Bill Brown
Wednesday, August 11, 2004

Aw shit.  Now someone tells me.  I was just naming stored procs with sp_* because I saw a bunch of examples like that somewhere.

We shoulda hired a DBA
Wednesday, August 11, 2004

Back years ago I implemented several systems where I dutifully named all of the stored procedures per the obvious standard of prefacing them with sp_ (Stored Procedure). I learned later that it was incorrect, and was surprized that-

a) Such a silly system is used to namespace separate procedures. We already have four-part naming, so I don't know why such an arbitrary shortcut was given.

b) Given this arbitrary namespace shortcut, why didn't the query parser spit up an error (perhaps with an override setting on the database) when an sp_ prefixed procedure was created?

I feel like an idiot for having used sp_, but at the same time I'd place a lot of the blame on Microsoft's shoulders.

One other quirk - if you preface a procedure with sp_ the execution rate will be marginally slower (if your procedure does absolutely nothing but returns, the call rate will take a 10% hit. If you actually do processing in the procedure, the impact will be signficantly less), presumably because it's checking master or whatever. Here's the weird thing, though: Create an sp_test in your database, and an sp_test in master -- despite the purported "run master first", it will run your local copy first, at least in my own testing. Perhaps it is my setting of database chaining or the like.

Dennis Forbes
Thursday, August 12, 2004

Man, thanks for that tid bit. I am fairly new to MSSQL. I've been working with Access and dabbling in MSSQL since 6.5.

I hadn't heard that information before and luckily I haven't created all that many procedures. I would've stuck to the naming convention thinking it was best to do out of principal or something not even realizing the prefixes made any difference in functionality.

Other than xp_ and sp_, are there prefixes that have functional purpose with regard to stored procedures?

Chance Govar
Thursday, August 12, 2004

Easiest way to name a procedure is basically the type of function that is going to be used.

SELECT blah1, blah2 FROM BLAHS

Stored procedure should be called:

select_blahs

or something to that effect.

An UPDATE would be update_blah, etc, etc.

Makes finding the sproc much simpler.

Jon
Thursday, August 12, 2004

Why not blahs_update and blahs_select (or more commonly blahs_get for a single item, and blahs_list for multiple items)? Seems that that organizes procs more logically.

Of course this falls apart when procs get more complex than simple CRUD type procs.

Dennis Forbes
Thursday, August 12, 2004

*  Recent Topics

*  Fog Creek Home