CODEWITHSUNDEEP
sqlsql-server
ylcnky
ylcnky

Reputation: 775

SQL variable as database name, Incorrect syntax error

I have the following query which I need to use variables as the database name. There are already number of questions about this. Although I followed them, I still have the [S0001][102] Incorrect syntax near 'varchar'. error. I guess that is sth related with text/characters in the query or sth else?

DECLARE @portalDB VARCHAR(60);
DECLARE @RemoteData_DB VARCHAR(60);
DECLARE @RemoteCenter VARCHAR(60);
DECLARE @RemoteCenterCMS VARCHAR(60);
DECLARE @SQL NVARCHAR(MAX);

SET @portalDB = ' [portal_QA]';
SET @RemoteData_DB = ' [RemoteData_QA]';
SET @RemoteCenter = ' [RemoteCenter_QA]';
SET @RemoteCenterCMS = ' [RemoteData_CMS_QA]';

SET @SQL = 'SELECT customer.id AS cus_id
        ,machine.serialNumber as dl_id
        ,office.id AS location_id
        ,equipment_info.id AS asset_id
        ,CONVERT(varchar(20), office.id)'+'_'+'CONVERT(varchar(20), equipment_info.id) AS lid_id
        ,equipment_info.equipment_type AS et
        ,tool_type.name AS ty
        ,equipment_info.nickname AS ni
        ,equipment_info.project_id AS pi
        ,equipment_info.serial_number AS sn
        ,equipment_info.last_updated AS lu
        ,equipment_info.siebel_id AS sb
        ,component.duty_class as dc
        ,equipment_info.description as desc
        ,equipment_info.date_created as scad
        ,conn.connection_id as c
        ,component.capacity as cp
        ,component.main_component as im
        ,component.component_id as ci
        ,component.id as id
        ,component.serial_number as c_sn
        ,cms_crane.rowId as cms_RowId
        FROM' + @portalDB +'.[dbo].customer AS customer
        INNER JOIN' + @portalDB +'.[dbo].office AS office ON office.customer_id = customer.id
         INNER JOIN'+ @portalDB +'.[dbo].equipment_group AS equipment_group ON equipment_group.office_id = office.id
         INNER JOIN'+ @portalDB +'.[dbo].equipment_info AS equipment_info ON equipment_info.equipment_group_id = equipment_group.id
         INNER JOIN'+ @portalDB +'.[dbo].component AS component ON component.equipment_info_id = equipment_info.id
         INNER JOIN'+ @portalDB +'.[dbo].connection conn ON conn.id = component.connection_id
         INNER JOIN'+ @RemoteData_DB +'.[dbo].Machine machine on machine.serialNumber = conn.connection_id
         INNER JOIN'+ @RemoteCenter +'.[dbo].ToolType AS tool_type ON tool_type.id = equipment_info.tool_type_id
          LEFT JOIN'+ @RemoteCenterCMS +'.[dbo].Tool AS cms_crane ON equipment_info.project_id = cms_crane.craneId';

exec sp_executeSQL @SQL
-- tried with exec(@SQL) also

Upvotes: 0

Views: 495

Answers (2)

Caius Jard
Caius Jard

Reputation: 74740

Slightly neater, harder to get wrong:

,CONCAT(office.id, ''_'', equipment_info.id) AS lid_id

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Why you are not using print @SQL before sp_executeSQL @SQL to check how your query generated, the error is at concatenation :

It would be double quotes :

. . . 
,CONVERT(varchar(20), office.id)+''_''+CONVERT(varchar(20), equipment_info.id) AS lid_id
. . .

Do not use reserved keywords for instance desc would needs to quote [desc].

Upvotes: 3

Related Questions