Using a variable for database name in select from table in a stored proc
Example stored Proc
I would like to select from the table ExportSettings from both within the current database & from another database on the same server
Real DATABASENAME is ST_v4051 but want to reference it as a variable
How do I resolve DATABASENAME?
Thank you & kind regards David
CREATE PROCEDURE [dbo].[MyBacgroundTask] As Begin Declare @col1 nvarchar(50), @col2 nvarchar(50) SELECT TOP (1) @col1 = col1, @col2 = col2 —FROM ST_v4051.dbo.ExportSettings FROM DATABASENAME.dbo.ExportSettings END
Best Answer
Use dynamic sql within your stored procedure
CREATE PROCEDURE [dbo].[MyBacgroundTask] ( @databasename nvarchar(128)) As Begin Declare @col1 nvarchar(50), @col2 nvarchar(50) declare @sql nvarchar(max) set @sql = 'select TOP (1) col1, col2 FROM ' + @databasename +'.dbo.ExportSettings' exec sp_executesql @sql end
