Solved

SQL stored procedure

Posted on 2011-09-28
5
210 Views
Last Modified: 2012-08-13
Is there a way to make the name of the database used in a stored procedure a variable.  Can I have 1 stored procedure that runs in different databases based upon the name of the  database name I pass in as a parameter.  

"a USE database statement is not allowed in a procedure, function or trigger."  = error from SQL
0
Comment
Question by:dastaub
5 Comments
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36716806
select * from your_database_name..your_table_name
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36716807
If you pass in the db name as a parameter, then you could try something like this in your code:

IF @db_name = 'db1' SELECT * FROM db1.dbo.MyTable
IF @db_name = 'db2' SELECT * FROM db2.dbo.MyTable
IF @db_name = 'db3' SELECT * FROM db3.dbo.MyTable
-- et cetera

Open in new window


If you do not want to specify the databases up front, you will have to use dynamic SQL.
0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 500 total points
ID: 36716836
Yes, but the simplest thing to use is dynamic SQL.
create procedure myProc (@nvchDB nvarchar(100))
as
begin
    exec ('select * from ' + @nvchDB + '.dbo.myTable')
end
go

Open in new window

0
 

Author Comment

by:dastaub
ID: 36716988
exec ('use ' + @nvchDB )

i tried this, but it appears to be ignored by the stored procedure.  if this worked, I would only have to change 1 line of code
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36717457
You can't do that.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

919 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

17 Experts available now in Live!

Get 1:1 Help Now