Different database in stored procedure [duplicate]

Question

I have following situation:

Input parameter in stored procedure is database name. So, depends on this parameter query is executed on different database. Every query is same, below is only simple example.

How to write stored procedure (except using dynamic sql) to avoid IF...ELSE statement like in code bellow.

ALTER PROCEDURE [dbo].[usp_Item_GetAll]
(
    @DBName nvarchar(255) = ''  
)
AS
BEGIN

IF @DBName = 'ItemUsers'
BEGIN
    SELECT
    *
    FROM ItemUsers.dbo.vW_DAM_ItemWithAttribute
END

ELSE IF @DBName = 'CollectionUsers'
BEGIN
    SELECT
        *
    FROM CollectionUsers.dbo.vW_DAM_ItemWithAttribute
END

I don't think there is another way besides dynamic sql and what you have done here. — liebs19
– liebs19, Commented Jul 2, 2013 at 21:01

Sachin · Accepted Answer · 2013-07-02 21:02:17Z

0

You may create a string that contains the dynamic query along with database name and then execute that query using EXEC command something like this

ALTER PROCEDURE [dbo].[usp_Item_GetAll]
(
    @DBName nvarchar(255) = ''  
)
AS
BEGIN

DECLARE @SQL varchar(MAX)
SET @SQL='SELECT * FROM ['+@DBName+'].dbo.[vW_DAM_ItemWithAttribute]'
EXEC(@SQL)
END

answered Jul 2, 2013 at 21:02

Sachin

41.1k7 gold badges94 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Bertrand Over a year ago

I would use QUOTENAME() instead of manually putting square brackets around it.

Collectives™ on Stack Overflow

Different database in stored procedure [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related