Different database in stored procedure

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, Jul 02 '13 at 21:01

score 0 · Answer 1 · answered Jul 02 '13 at 21:02

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 02 '13 at 21:02

Sachin

40,216
7
90
102

2

I would use `QUOTENAME()` instead of manually putting square brackets around it. – Aaron Bertrand Jul 02 '13 at 21:04

Different database in stored procedure

1 Answers1