Solved

Stored procedure question

Posted on 2004-10-13
20
257 Views
Last Modified: 2008-02-07
I have a problem that im trying to solve. We have lets say 3 Databases

All these databases have the same Table "Contacts" It has the same structure (FirstName,Lastname,address,etc....) What I want to do is have one stored procedure somewhere instead of three stored procedures in the 3 different databases.

Example
DB Names (Same instance of SQLserver)
Test_DB1
Test_DB2
Test_DB3

All these 3 databases have the same contacts Table.

How could I use one stored procedure for the 3 databases?

So if I called lets say SP_getallcontacts and im currently connected (IN ASP) to Test_DB1 how can that stored procedure know that I want to get the contacts from Test_DB1?
0
Comment
Question by:Nagas062301
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 3
  • +1
20 Comments
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12300530
reference your database name and table

Select *
From  serverName.Test_DB1.dbo.contacts

Select *
From  serverName.Test_DB2.dbo.contacts

Select *
From  serverName.Test_DB3.dbo.contacts

 
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12300583
If all three databases are under the same server... then inside your SP

Select *
From  Test_DB1.dbo.contacts

Select *
From  Test_DB2.dbo.contacts

Select *
From  Test_DB3.dbo.contacts
0
 

Author Comment

by:Nagas062301
ID: 12300604
Yes I tried this but I would have to have rewrite 394 SP's. and make them dynamic which is a pain in the a$$.

thers got to be another solution
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Nagas062301
ID: 12300651
If the stored procedure recided in the Master DB is there any way it could use the same stored procedure?

Lets say the SP is like this

CREATE PROCEDURE sp_getallcontacts

AS

Select * from contacts

Go

So when i connect to Test_DB1 I would call sp_getallcontacts it should know that im going to select contacts from the Test_DB1
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12300892
Don't think so!

If you don't want to mess around with dynamic query, about the only other way to do this is..... when call a SP, pass some value into the SP such as

Exec sp_getallcontacts Dbase_Ind

CREATE PROCEDURE sp_getallcontacts
@Dbase_Ind varchar(20)
AS

IF @Dbase_Ind = 'Test_DB1'
   Begin
     Select *
     From  Test_DB1.dbo.contacts
   End

IF @Dbase_Ind = 'Test_DB2'
   Begin
     Select *
     From  Test_DB2.dbo.contacts
   End

IF @Dbase_Ind = 'Test_DB3'
   Begin
     Select *
     From  Test_DB3.dbo.contacts
   End
Go

You can create this SP in any database.




0
 

Author Comment

by:Nagas062301
ID: 12301342
Still not an option the DBs will continue to be created so I would have to edit the SP every single time we create a new database.

Is there no way to have SPs sort of be in one place and the DB's in another?

Sort of

SPDB
Test_DB1
Test_DB2
Test_DB3

So I would have like a db of SPs and the databases seprated???? Theres got to be a way
0
 
LVL 8

Expert Comment

by:SNilsson
ID: 12301720
How about this one then:

_____________________________________
CREATE PROCEDURE sp_getallcontacts
@DbName varchar(128)
AS

SET @DbName = 'Select * From  ' + @DbName + '..contacts'

SELECT * FROM OPENROWSET('SQLOLEDB','SERVERNAME';'USERID';'PASSWORD',@DbName)
_____________________________________

Just change servername, userid and password and it should work.
0
 

Author Comment

by:Nagas062301
ID: 12302008
I dont want to select all the databses and loop thru them. I just want to be able to get the contact from Test_DB1 if im connected to it using ONE SP thats in the master DB or wherever I need to put them.

Am i explaining this wrong or is has none ever done this?
0
 

Author Comment

by:Nagas062301
ID: 12302196
Maybe I need to explain this differently.

The app I have created created a new Database for each customer that signs up.

So today lets say I have Cutst_1 DB. It has lets just say ONE table  in it that has his contacts (Contacts).

It also has ONE SP called sp_getallcontacts.

Tomorrow another clients signs up and a new DB gets created on the fly
Cutst_2
With a SP called sp_getallcontacts

Now lets say 5 other Customers have signed up

Si now we have
Cutst_1
Cutst_2
Cutst_3
Cutst_4
Cutst_5

They all have the same SP sp_getallcontacts (So total sp's are 5)

So what happends is if we update the code and need to add something to the SP we have to change 5 sp's. Thats become an issue since we expect the dbs to grow it get difficult to update all the Sp's

I would like to have all the SP's on one db but be able to call ANY Cutst_. Is there a way to do this?????
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 125 total points
ID: 12303569
Yes.  You must do these things:

1) Put the sp in the master db.
2) Begin the name with sp_.
3) Mark it as a "system" procedure.


So:

USE master
GO
CREATE PROCEDURE sp_GetAllContacts
...
GO
EXEC sp_MS_MarkSystemObject 'sp_GetAllContacts'


Then, when you do this (or the equivalent):

USE test_db1  --or otherwise make test_db1 the current db
EXEC sp_GetAllContacts --will use/return data from test_db1

USE test_db2
EXEC sp_GetAllContacts --will use/return data from test_db2

and so on.
0
 
LVL 8

Expert Comment

by:SNilsson
ID: 12305091

Hmm, I belive I understod you correctly the first time, you need  call the procceduere I wrote above like this from your application.

____________________________________________________________
Execute master..sp_GetAllContacts @DbName = sMyDB
____________________________________________________________

Where sMyDB is a variable in your application that will hold the current db in use, Cutst_3 for example.
0
 

Author Comment

by:Nagas062301
ID: 12307513
Thanks for your help guys/gals


 ScottPletcher. How do you mark a stored procedure as a system stored procedure?

I created the SP in the master but I dont see a way of making it a system type procedure.

0
 

Author Comment

by:Nagas062301
ID: 12307761
the SP sp_MS_MarkSystemObject doesnt excist
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12308087
>> I created the SP in the master but I dont see a way of making it a system type procedure. <<
>> the SP sp_MS_MarkSystemObject doesnt exist <<

It works fine on my systems, which are all SQL 2K.  I think it worked on 7.0 also, but can't remember for sure.

Use Query Analyzer (*not* EM), and type in these commands:

USE master
EXEC sp_MS_MarkSystemObject 'sp_GetAllContacts'

-- The sp_MS_MarkSystemObject should change color to indicate that SQL "recognizes" it (assuming you've left those defaults for QA alone).

Naturally the sp must already exist in the *master* db before you can mark it.  And, AFAIK, the name must begin with "sp_".
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12308114
Oh, right, the reason for this is that otherwise SQL will not use the context of the db you're in but will use the context of the db the sp is in.  That is, if you do this:

USE master
GO
CREATE PROCEDURE sp_zListObjects
AS
SELECT *
FROM sysobjects


Before marking it as a system object, if you do this:

USE northwind
EXEC sp_zListObjects

The list of objects is still from the *master* db.

But, if you do this:

USE master
EXEC sp_MS_MarkSystemObject 'sp_zListObjects'

and it works ( :-) ), then when you do this:

USE northwind
EXEC sp_zListObjects

You will see the northwind objects, which is what you want.
0
 

Author Comment

by:Nagas062301
ID: 12308162
Now thats funny. Once the SP is made into a System SP you cant delete or edit. Darn there goes another Idea.


AAAAAAARERRRRRGGG
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12308203
Actually, you can't delete it *thru EM* -- you've got to get away from the "training wheels".  You should be able to DROP it from QA.
0
 

Author Comment

by:Nagas062301
ID: 12308231
How do you edit the SP after its been converted to a system SP?

Again thanks for your insight Scott
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12308239
Btw, you can also do this:

EXEC northwind.dbo.sp_zListObjects

That is, you can specify the db context directly on the statement itself.  Sometimes this can come in handy if you want to run it dynamically if a variable context, for example:


CREATE PROCEDURE up_XXX
    @dbName SYSNAME
AS
-- exec "system" sp in context of variable db name
EXEC(@dbName + '.dbo.sp_zListObjects')
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12308248
>> How do you edit the SP after its been converted to a system SP? <<

You have to drop and re-create it, using QA.  AFAIK you can't ALTER a sp that has been marked as "system".  To me that's a small price to pay for the gain you get ...
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question