SQL variable as database name, Incorrect syntax error

Question

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

Simplify the query. Remove stuff not needed to reproduce the problem. — jarlh
– jarlh, Commented Nov 15, 2018 at 9:37
Add some sample table data and the expected result - all as formatted text, not images. — jarlh
– jarlh, Commented Nov 15, 2018 at 9:37
What is CONVERT(varchar(20), office.id)'+'_'+'CONVERT(varchar(20), equipment_info.id) meant to be doing? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 15, 2018 at 9:41
You should really be using QUOTENAME here on your dynamic object names. Otherwise this could very easily be open to Injection. — Thom A
– Thom A ♦, Commented Nov 15, 2018 at 9:47
@Larnu I will consider your suggestion for QUOTNAME, thanks. — ylcnky
– ylcnky, Commented Nov 15, 2018 at 10:35

Yogesh Sharma · Accepted Answer · 2018-11-15 09:41:11Z

3

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].

answered Nov 15, 2018 at 9:41

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

Comments

Caius Jard · Accepted Answer · 2018-11-15 09:48:14Z

0

Slightly neater, harder to get wrong:

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

answered Nov 15, 2018 at 9:48

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Collectives™ on Stack Overflow

SQL variable as database name, Incorrect syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related