user3859666 user3859666 - 4 months ago 15
SQL Question

Expected ID or Quoted_ID in SQL

I am receiving the following error:

"Incorrect Syntax near '.'. Expected ID or Quoted_ID".


When trying to create the below procedure,

CREATE PROCEDURE [dbo].[SP_NAME]
@DBName varchar(max)
As
Begin

SELECT NBCGL_TRANSACTION.*, MRG_BSLA.GL_BSLA_CODE AS BSLA
FROM @DBName..NBCGL_TRANSACTION LEFT OUTER JOIN MRG_BSLA
ON NBCGL_TRANSACTION.BUSINESS = MRG_BSLA.BSLA_CODE
WHERE NBCGL_TRANSACTION.TRANSACTION_TYPE = 2

END

Answer

You can't use variables for the database/schema/table/column names directly. You need to use dynamic SQL to achieve your goal:

CREATE PROCEDURE [dbo].[SP_NAME]
@DBName varchar(max)
As
Begin

declare @query nvarchar(max) 
set @query = N'SELECT NBCGL_TRANSACTION.*, MRG_BSLA.GL_BSLA_CODE AS BSLA FROM ' 
+ @DBName
+ N'..NBCGL_TRANSACTION LEFT OUTER JOIN MRG_BSLA ON NBCGL_TRANSACTION.BUSINESS = MRG_BSLA.BSLA_CODE WHERE NBCGL_TRANSACTION.TRANSACTION_TYPE = 2' 

exec sp_executesql @query

END