question

DavidG avatar image
DavidG asked

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

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

1 Answer

·
Kev Riley avatar image
Kev Riley answered

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 comment
10 |1200

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

Thank you Kev

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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