• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

SQL stored procedure

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
dastaub
Asked:
dastaub
1 Solution
 
Kiran SonawaneProject LeadCommented:
select * from your_database_name..your_table_name
0
 
Patrick MatthewsCommented:
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
 
Lee SavidgeCommented:
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
 
dastaubAuthor Commented:
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
 
Lee SavidgeCommented:
You can't do that.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now