SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure & 'other' database


Stored Procedure & 'other' database

Author
Message
R. van Laake
R. van Laake
Old Hand
Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)

Group: General Forum Members
Points: 363 Visits: 104
Hi,

I have four clients, each with their own identical database.
They all have the same Stored Procedure named sp_Test.

To simplify things, I want a new database with sp_Test, that is called by the clients.

So,I made a new database called COMMON, and put sp_test in there. Problem is, this Stored Procedure doesn't know on which database to perform its actions. A statement like USE DB_CLIENT_ONE gives an error ("USE database statement is not allowed in a procedure").

It's annoying that such a Stored Procedure can do the trick, for example:

SELECT * FROM DB_CLIENT_ONE.DBO.orders
this works

USE DB_CLIENT_ONE
SELECT * FROM orders
this doesn't work

Is there a way to get it done, other than with the EXEC statement?

Thanks,
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13285 Visits: 23078
I don't advocate adding procedures to the Master database, but this will solve your issue.
If you add a procedure to the Master database (and the dbo schema) and use a name starting with "sp_", it will be found when executing it from another database but run in the context of the database it is called from.

For example:

USE Master
GO
CREATE PROCEDURE dbo.sp_TestMe
AS
SELECT DB_Name()
GO
USE MyDatabase --Replace with your database name
GO
EXEC sp_TestMe
r.vanlaake
r.vanlaake
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 14
Thanks Michael, it looks like this will indeed solve the issue.

One question though: you say you "don't advocate adding procedures to the Master database". Can there be serious problems doing so? I never touched the master database before, so I'm not an expert in this area....

Thanks again,.
Raymond
SQL_ABD
SQL_ABD
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2198 Visits: 1349
Hi,
I think you can use dynamic SQL

alter proc Test_SP
@dbName Sysname
As

Declare @strSQL Nvarchar(200)
set @strSQL ='Select * FROM ' + @dbName + '.dbo.orders'
exec sp_executesql @strsql

Go

Regards,
Ahmed
Suresh B.
Suresh B.
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4998 Visits: 5329
Instead of dynamic SQL, I recommend conditional execution like this:

alter proc Test_SP
@dbName Sysname
As
if @dbName = 'Database1'
begin
select * from Database1.dbo.TableName
end
else if @dbName = 'Database2'
begin
select * from Database2.dbo.TableName
end

Go
SQL_ABD
SQL_ABD
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2198 Visits: 1349
Sorry Suresh,
But if you will have more than 4 clients, will you still using conditional execution.
Good luck

Regards,
Ahmed
Suresh B.
Suresh B.
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4998 Visits: 5329
Hi Ahmed,

When a new database is created for the new client, this stored procedure can be altered.

As dynamic sql is not precomplied, I think conditional execution is faster.
However, I have not compared the performance of these 2 types of stored procedures.

Thanks.

Suresh
SQL_ABD
SQL_ABD
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2198 Visits: 1349
Yes, you are rigth on this point 'dynamic sql is not precomplied'
Ahmed
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13285 Visits: 23078
You are not likely to run into errors from adding procedures to the master database.
What you do have to watch out for is making sure you appropriately back up your system databases, be aware that if you transfer a database to another server that you may have broken some logic. You also have to watch out for a service pack or something that could potentially replace or remove your stored procedure.

I don't like adding objects to the system databases mostly because they are system databases and I like to leave them alone. For what you are doing, I probably would just put the procedure into the master database and ignore all of the dynamic SQL or conditionals. However, if you find yourself adding more than 10 or so procedures to the system databases, I would say you may have a design problem.
r.vanlaake
r.vanlaake
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 14
Thanks again Michael (and others)

My preference indeed is to skip the conditionals and the dynamics. On the other hand, the number of stored procedures would run into the 100's. I am building an ASP website, and all calls to the database are stored procedures (to make a conversion to asp.net later more simple).

On the ASP side, all customers have their own website. Every .asp file basically consists of two lines:
#include customer settings
#include common file

So, with any number of customers, there's always just one file for me to update (the common file).

Obviously I would like to have the same type of setup for the database. It would be a drag to have to update the same Stored Procedure 50 times for each of the 50 customers.

Probably the best would be is, after a Stored Procedure has been created or modified in a test enviroment, run a script that creates/modifies all the counterparts in the customer databases.

Thanks,
Raymond
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search