Solved

Need a SQL stored procedure to use tables from multiple databases

Posted on 2009-05-14
4
826 Views
Last Modified: 2012-05-07
I have a stored procedure that I would like to pass in a database name as a parm and have that database be used for all the tables. Other then scripting each and every SQL statement does anyone know of a way to do this? The USE statement would be ideal but unfortunately you can't use it in a stored procedure.
0
Comment
Question by:Bob Hoffman
  • 2
4 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24385507
You can try with dynamic SQL:

DECLARE @DbName NVARCHAR(100), @Sql NVARCHAR(MAX)
SET @DbName = 'MyDatabase';

SET @Sql = 'SELECT ColumnName FROM ' + @DbName + '.dbo.Your_Table_Name'

EXEC(@Sql)
0
 
LVL 8

Author Comment

by:Bob Hoffman
ID: 24385599
That's what I meant by scripting the SQL statements. Normally I would do it that way but there are several DECLARE statements which would cause me problems. Thanks
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24385775
I think this will workout for you (I never tried it )
Create the stored procedure with 'sp_' in the master database and make this sp system sp
If you are using sql2005 , use the below

USE MASTER
EXECUTE sp_ms_marksystemobject 'urSpName'  
 
and call it from ur databases
0
 
LVL 8

Accepted Solution

by:
Bob Hoffman earned 0 total points
ID: 24458215
Found my own solution, Here is what I did:

--you can run multiple statements just seperate them with a semi colon

CREATE PROCEDURE [dbo].[run_statement]
            @database varchar(50),
            @exesql varchar(4000)
                       
AS

DECLARE @sql varchar(4000)

--Set database context, execute statement(s)
SET @sql = @database + '..sp_executesql @statement = N''USE ' + @database + '; ' + replace(@exesql,'''','''''') + ';'''
EXEC (@sql
 
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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

18 Experts available now in Live!

Get 1:1 Help Now