Variable database name in stored procedures

[ssouhrada]

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_proc edure_name
AS

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

Begin

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

end
GO

[debasisdas]

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

[ssouhrada]

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