Variable database name in stored procedures

Question posted by: ssouhrada (Newbie) on June 30th, 2008 12:19 PM

I am trying to create a stored procedure that runs a smaller query first to determine the database to run the second query on. Below is a sample query that is similar to what I would be doing. I'm not interested in creating new Stored Procedures as their are hundreds that are like this so I don't want to write new statements like...

EXEC( "Select * from " + @TableName + " where " + CAST(@Param1 as varchar) +
" = " + @Param2
GO

as that would be too many queries to update. Any thoughts?

Thanks

CREATE PROCEDURE dbo.stored_procedure_name
AS

DECLARE @database varchar(50)
SET @database = (SELECT dbName FROM database.currentDatabase)

Begin

set nocount on
select * from (@database).dbo.table_name

end
GO

debasisdas

Moderator

6,576 Posts

July 2nd, 2008
09:30 AM
#2

Re: Variable database name in stored procedures

That would be better with a stored procedure.
You can also try to use nested sub-queries.

ssouhrada

Newbie

7 Posts

July 2nd, 2008
12:35 PM
#3

Re: Variable database name in stored procedures

Quote:

That would be better with a stored procedure.
You can also try to use nested sub-queries.

Do you have an example that you could post? The only way I could find out how to do this was using an exec statement.

Exec( 'Select * from ' + @database + '.dbo.TableName')

That is essentially in a stored procedure but it compiles at run time so the benefit of having it in a stored procedure isn't utilized. If you have any examples of how to do this I would appreciate it.

Thanks

Not the answer you were looking for? Post your question . . .
189,798 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).

Latest Articles: Read & Comment

Top Microsoft SQL Server Contributors