SQl server- writing stored procedure using 'USE [db name]'

Question

I am writing a stored procedure which will compare two similar tables under two different database. Here I am using the keyword USE [dbname].

DECLARE 

--INPUT

@BASE_DATABASE_NAME NVARCHAR(50),

@TARGET_DATABASE_NAME NVARCHAR(50),

@TARGET_PRODUCT_ID NVARCHAR(50),

@TARGET_PRODUCT_CODE NVARCHAR(50)

SET @BASE_DATABASE_NAME='USE [DB1]'

SET @TARGET_DATABASE_NAME='USE [DB2]'

SET @TARGET_PRODUCT_ID=4194


SET @TARGET_PRODUCT_CODE = @BASE_DATABASE_NAME ( SELECT PRODUCT_CODE FROM T_PRODUCT_MST WHERE PROD_ID = @TARGET_PRODUCT_ID)

print @TARGET_PRODUCT_CODE.

Error--

It's not working...

Can anybody help me out with this? I need to pass the database name dynamically to the sql query.

Thanks in advance..

Can you please provide the exact error message? – BAdmin Feb 04 '14 at 09:13 — BAdmin, Feb 04 '14 at 09:13

Szymon · Answer 1 · 2014-02-04T09:28:14.360

2

You cannot use USE this way. USE sets the database against which all the statements are executed and cannot be used inside another query.

You can use dynamic SQL though to specify your query:

DECLARE 

--INPUT

@BASE_DATABASE_NAME NVARCHAR(50),

@TARGET_PRODUCT_ID INT,

@TARGET_PRODUCT_CODE NVARCHAR(50)

SET @BASE_DATABASE_NAME='[DB1]'

SET @TARGET_PRODUCT_ID=1

DECLARE @SQL NVARCHAR(MAX) = N'SELECT @TARGET_PRODUCT_CODE = PRODUCT_CODE FROM ' 
    + @BASE_DATABASE_NAME 
    + N'..T_PRODUCT_MST WHERE PROD_ID = @TARGET_PRODUCT_ID'

exec sp_executesql @SQL, N'@TARGET_PRODUCT_ID INT, @TARGET_PRODUCT_CODE NVARCHAR(50) OUTPUT',
    @TARGET_PRODUCT_ID, @TARGET_PRODUCT_CODE OUTPUT

print @TARGET_PRODUCT_CODE

edited Feb 04 '14 at 09:28

answered Feb 04 '14 at 09:15

Szymon

42,577
16
96
114

Thanks Szymon!! It really helping me a lot!! But in my stored procedure I am storing the result into one variable i.e. @TARGET_PRODUCT_CODE which I will use it in other query and your stored procedure didnt stored the result. Please look into it.. Thanks again!! – user3270051 Feb 04 '14 at 10:20
I tested that code and it works for me. You need to use `OUTPUT` to return the value of the variable. – Szymon Feb 04 '14 at 10:36

score 0 · Answer 2 · answered Feb 04 '14 at 09:21

Another option to Szymon's answer is to use synonyms. First create your synonyms in the DB:

CREATE SYNONYM [dbo].[TargetProductCode] FOR [DB2].[dbo].[T_Product_MST]

And then your sql syntax becomes:

SET @TARGET_PRODUCT_CODE = SELECT PRODUCT_CODE 
FROM dbo.TargetProductCod WHERE PROD_ID = @TARGET_PRODUCT_ID

If this doesn't need to be dynamic, this can be a good solution, and can also make for easier testing, if for some reason you need to point a test DB to a different target (can just update the synonym).

SQl server- writing stored procedure using 'USE [db name]'

2 Answers2