Sql-server – How to use local variable in USE statement

sql servert-sql

How to use local variable in USE statement? I get error:

Msg 911, Level 16, State 1, Line 6
Database 'DWSource_@Country' does not exist. Make sure that the name is entered correctly.

Code:

DECLARE @Country varchar(3)
SET @Country='UKR'

USE DWSource_@Country

SELECT  @Country as country,
        count(*) as n_ALL,
        sum(case when LEN(EPOSTA)>0 then 1 else 0 end) as EMAIL1,
        sum(case when LEN(EPOSTA_2)>0 then 1 else 0 end) as EMAIL2

FROM    [dbo].[_data_CRM_S_PARTNER] 
WHERE   VIR = 'SM'
    AND PRAVNA_OSEBA = 0
    AND PROBLEMATICEN = 0   
    AND ISNULL([STATUS],0) IN (0,1) 
    AND ID_DBCLUSTER IN ('112','122','132','212','222','232','312','322','332','311','321','331')

Thanks in advance!

Br,
Anja

Best Answer

You can't pass a variable to USE. You can do this inside the dynamic SQL, or you can build a string that prepends each object name with the database prefix.

A cleaner dynamic SQL approach than the latter approach, IMHO, since it doesn't require you to inject the database name in front of every object in the dynamic SQL (and also, in this case, allows you to pass @Country as a parameter):

DECLARE @country CHAR(3) = 'UKR';
-- assume above is incoming parameter

DECLARE
  @db SYSNAME = N'DWSource_' + @Country,
  @exec NVARCHAR(300), 
  @sql NVARCHAR(MAX);

SET @sql = N'SELECT  @Country as country,
        count(*) as n_ALL,
        sum(case when LEN(EPOSTA)>0 then 1 else 0 end) as EMAIL1,
        sum(case when LEN(EPOSTA_2)>0 then 1 else 0 end) as EMAIL2
FROM    [dbo].[_data_CRM_S_PARTNER] 
WHERE   VIR = ''SM''
    AND PRAVNA_OSEBA = 0
    AND PROBLEMATICEN = 0   
    AND ISNULL([STATUS],0) IN (0,1) 
    AND ID_DBCLUSTER IN (''112'',''122'',''132'',''212'',''222'',
      ''232'',''312'',''322'',''332'',''311'',''321'',''331'')';

SELECT @exec = QUOTENAME(@db) + '.sys.sp_executesql'
EXEC @exec @sql, N'@Country CHAR(3)', @Country;

Both of those things at least somewhat reduce your risk for SQL injection, IMHO. Not that a malicious user could do much with a CHAR(3) but if you use that pattern in other ways with bigger variables, someone could easily pass @Country = '1''; DROP TABLE dbo._data_CRM_S_PARTNER; SELECT '''... and in the other solution this would be a problem (again, if the variable were bigger).

EXEC @exec method quasi-stolen from Erland Sommarskog's great article on dynamic SQL.

That all said, I do agree with Thomas that if you have the same query you want to run in multiple databases, you are better off creating the same stored procedure in each database, and having the app just connect to the desired database in the first place. Since the app has to know which country you're after, it shouldn't be that hard to just generate a connection string based off of that, and call a clean, no-dynamic-SQL stored procedure in that database.

Related Question