Expected Identifier or Quoted_ID in SQL

Question

I get the following error:

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

When you try to create the procedure described below

 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 1

You cannot directly use variables for database / schema / table / column names. To achieve your goal you need to use dynamic SQL:

 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