Telling a Stored Procedure Which Database to Use

Hi DrMaltz,

This is the technique I used for my last web development job.  I never liked it because of the fact that the SQL statement is now dynamic instead of being pre-compiled by the stored procedure.  Is this the only way to achieve the objective?  Thanks.


Jazon

If you create your stored procedure in the master database, and use the prefix "sp_" for the Name (like sp_CreateCustomer), then the stored procedure will be known by all your database on that server, and the context will remain the database from which you called the stored procedure.
To test this, try this script:


USE MASTER
CREATE PROCEDURE sp_WhichDB
AS
SELECT db_name()
GO
USE pubs
EXEC sp_WhichDB
GO

This should print out "pubs"...

CHeers

This is really cool.  I didn't know you can do this.  Thanks.  I've always been told, however, to never play with the master database, since the consequences could be tragic.  Would you folks actually start creating personal stored procedures in the master database, or is this a no-no in a good system infrastructure?

piratepatrol,

Some of these questions have been open for some time, and records indicate you logged in as recently as a today. Please resolve them appropriately as soon as possible. Continued disregard of your open questions will result in the force/acceptance of a comment as an answer; other actions affecting your account may also be taken. I will revisit these questions in approximately seven (7) days.

https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20260974 (new)
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20258569 (new)
https://www.experts-exchange.com/jsp/qShow.jsp?ta=cplusprog&qid=20174572
https://www.experts-exchange.com/jsp/qShow.jsp?ta=javascript&qid=20168259
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20159537
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20232167

Thanks,

Netminder
Community Support Moderator
Experts Exchange

I have had support cases with Microsoft, and generally they did not particulary disapprove of doing this, they did not support querying system tables (since there are stored procs that will do this for you). A word of warning (and where I came unstuck) is that you should ensure that you backup the master database and create scripts of your stored procs.

i agree with TheSpirit!

Hi friends,

I don't think having the stored procedures in master would achieve what I'm trying to do.  Right now, I have three separate databases, each one having the same exact tables.  Now instead of having the stored procedure codes repeated in all three databases, I would like for the stored procedures to reside in just one location.  But if they're all in one location, each time they're called, they have to be told which database called them.  Now here's the problem if I have the stored procedures in master: if ASP_1, which uses database_1, calls a stored procedure in master, that stored procedure doesn't know that it's supposed to use database_1.  When I tried it, the stored procedure said the specified table doesn't exist.  It looks to me that the name of the calling database has to be sent to the stored procedure as an input parameter.  This in turn will have to turn the stored procedure into having dynamic SQL instead of precompiled.  Any thoughts, my friends?

Jazon

hmm,

on one hand you want to pre-compile code - good for performance, on the other hand you would prefer no stored procedures on 2 out of 3 databases, that makes those 2 databases free of a pre-compiled code, because stored procedures are compiled only for local servers. If you care about good performance you would find some neat way to keep procedure code in synch, and deploy code on all three databases. On the opposite, if you decide to keep only single procedure for all three databases, advice of DrMaltz is sound one on my view. Personally, I would encourage you to go for performance advantage, even not so much for the pre - compiled code, but, for the sake of been able to use indexes. Remote queries can not use indexes, but the power of locally compiled stored procedure, is you can call it half way around the globe, and it will return data using good indexes on this database.

Cheers

I use extended stored procedures to do this.

We have a similar situation where we have multiple identical databases for different companies.

I pass in the database name as a parameter to the extended stored procedure call along with any other data that needs to be passed in.

Extended Stored Procedures are stored in DLLs written in C or VB.  They are compiled and then referenced from the master database.

This might do what you want.

good luck

Jim

Hi Jim,

This sounds like an awesome idea!  Is there an article somewhere I can read that can teach me how to make an extended stored procedure with VB?  This would really help me out.

Thanks, Jim.


Jazon

PiratePatrol,

You don't make extended stored procedures with VB.. Extended Stored Procs are developed with C/C++ in the form of a dll.  Now another option since you sound like you're VB savy, is to develop a COM dll in VB.  This COM Dll can then be called from within a stored procedure.. Check out this article..

http://www.swynk.com/friends/achigrik/OLEAutomationSP.asp

In my opinion, I still think you're better off with my first suggestion..

Good Luck,

DrMaltz

Microsoft documentation states that extended stored procedure can be written in any language capable of using windows API. That makes VB a very potent alternative. I tried to write an example, just to see how extended procedure works in Visual Basic, but stalled on the SRV_PROC structure handle. Now, that one is pronounced by Microsoft as a "hidden" and "undocumented" from SQL Server version 7.0 and up.

Jazon,

we used to have a programmer here that did know how to created an extended_stored_procedure with Visual Basic.   We have lost track of him.   He did used to get alot of his information from VBNet.

If you want to stay in VB, DrMaltz probably has the way to go, but if you can write in C, there are some good examples in msdn that should get you going.

You folks are so wonderful - thanks everyone.

piratepatrol,

Not to be a nag or anything... but can you please look at the open questions list?

Thanks,

Netminder
Community Support Moderator
Experts Exchange