Expected ID or Quoted_ID in SQL

Question

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

Solution

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

Comments

[Jon]

things like this kill me