Using a variable for database name in select from table in a stored proc

Question

question

DavidG asked Nov 5, '18

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

stored procedure

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-11-05T09:07:38Z

Kev Riley answered Nov 5, '18

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

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DavidG · Nov 06, 2018 at 07:16 AM

Thank you Kev

0 ·

question