Solved

Stored procedure question

Posted on 2004-10-13
20
247 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
  • 9
  • 6
  • 3
  • +1
20 Comments
 
LVL 8

Expert Comment

by:MartinCMS
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Nagas062301
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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:
ScottPletcher earned 125 total points
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 8

Expert Comment

by:SNilsson
Comment Utility

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
Comment Utility
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
Comment Utility
the SP sp_MS_MarkSystemObject doesnt excist
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> 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:ScottPletcher
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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:ScottPletcher
Comment Utility
>> 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now