title: Syntax errors in dynamic SQL with database name as variable
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/3943823/sql-server-how-to-get-a-database-name-as-a-parameter-in-a-stored-procedure">SQL Server: how to get a database name as a parameter in a stored procedure</a>  </p>
  </blockquote>
  
  
  
  <p>In the below stored procedure I am passing the database name as a parameter, but currently for testing I am database name via string which is declare in the procedure. </p>
  
  <p>Please advise as I get an error in <code>SET</code> syntax and it's not able to recognise the db name I am passing.</p>
  
  <pre><code>USE [smtpFetch]
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER Procedure [dbo].[checkForUpdateV]
  (
      @ctsCode Varchar(100)
  )
  As
  
  BEGIN
  
  Declare @dbName varchar(100)
  Declare @stDB TABLE
  (
  change_version_state varchar(max),
  change_version_status varchar(200),
  uniqueid  varchar(20),
  Country_code  varchar(15),
  Research_date  varchar(15),
  data_date  varchar(15),
  Make  varchar(50),
  Model  varchar(50),
  Versions varchar(255),
  Model_year varchar(15),
  doors varchar(5),
  body varchar(5)
  );
  DECLARE @number_days as INT
  DECLARE @DATAD as nvarchar(max)
  DECLARE @DATAD2 as varchar(max)
  SET @number_days = 5
  SET @ctsCode=@ctsCode
  SET @dbName='SSCRUS_CS2002'
  SET @DATAD =N'Delete from ' + @ctsCode
  select @DATAD
  --print @dbName;
  set @DATAD2=@DATAD2
  INSERT INTO @stdb
  set @DATAD2 = "SELECT  
    STUFF(
                (     SELECT ', ' + st105.full_text 
                      FROM"  + @dbName + ".dbo.version v2 
                      JOIN " + @dbName + ".dbo.equipment e105
                      ON e105.vehicle_id = v2.vehicle_id AND e105.schema_id = 105 
                      JOIN " + @dbName + ".dbo.schema_text st105  
                      ON st105.schema_id = 105 AND st105.data_value = e105.data_value AND    st105.language_id = 19 
                      WHERE   (DATEDIFF(day, { fn NOW() }, CONVERT(datetime, CONVERT(char(8), v2.id_103))) &gt; - @number_days) AND 
                                  v.vehicle_id = v2.vehicle_id
                      FOR XML PATH('')
                ),1,1,''
                ) AS Change_version_state,
  
    STUFF(
                (     SELECT ', ' + st106.full_text 
                      FROM " + @dbName + ".dbo.version v3 
                      JOIN " + @dbName + ".dbo.equipment e106
                      ON e106.vehicle_id = v3.vehicle_id AND e106.schema_id = 106 
                      JOIN " + @dbName + ".dbo.schema_text st106  
                      ON st106.schema_id = 106 AND st106.data_value = e106.data_value AND st106.language_id = 19 
                      WHERE   (DATEDIFF(day, { fn NOW() }, CONVERT(datetime,  CONVERT(char(8), v3.id_103))) &gt; - @number_days) AND 
                                  v.vehicle_id = v3.vehicle_id
                      FOR XML PATH('')
                ),1,1,''
                ) AS Change_version_status,
    v.id_101 AS [Unique ID], 
    v.id_109 AS Country, 
    v.id_103 AS [Research Date], 
    v.id_104 AS [Data Date],
    v.id_128 AS Make, 
    v.id_129 AS Model, 
    v.id_131 AS Version, 
    v.id_108 AS [Model Year], 
    v.id_605 AS Doors, 
    v.id_606 AS Body
  
    FROM " + @dbName + ".dbo.version v   
    WHERE   (DATEDIFF(day, { fn NOW() }, CONVERT(datetime, CONVERT(char(8), v.id_103))) &gt;  - @number_days)
    ORDER BY [Unique ID], Change_version_status, Change_version_state"
    execute sp_executesql @DATAD2
    --select * from @stdb
    END
  </code></pre>
  
  <p>Above stored procedure gives an error</p>
  
  <pre><code>Msg 156, Level 15, State 1, Procedure checkForUpdateV, Line 41
  Incorrect syntax near the keyword 'set'.
  
  Msg 103, Level 15, State 4, Procedure checkForUpdateV, Line 45
  The identifier that starts with '.dbo.equipment e105
  ON e105.vehicle_id = v2.vehicle_id AND e105.schema_id = 105 
  ' is too long. Maximum length is 128.
  
  Msg 103, Level 15, State 4, Procedure checkForUpdateV, Line 47
  The identifier that starts with '.dbo.schema_text st105  
  ON st105.schema_id = 105 AND st105.data_value = e105.data_value 
  AND st105.lang' is too long. Maximum length is 128.
  
  Msg 103, Level 15, State 4, Procedure checkForUpdateV, Line 58
  The identifier that starts with '.dbo.equipment e106
  ON e106.vehicle_id = v3.vehicle_id AND e106.schema_id = 106 
  ' is too long. Maximum length is 128.
  
  Msg 103, Level 15, State 4, Procedure checkForUpdateV, Line 60
  The identifier that starts with '.dbo.schema_text st106  
  ON st106.schema_id = 106 AND st106.data_value = e106.data_value 
  AND st106.lang' is too long. Maximum length is 128.
  
  Msg 103, Level 15, State 4, Procedure checkForUpdateV, Line 78
  The identifier that starts with '.dbo.version v   
  WHERE   (DATEDIFF(day, { fn NOW() }, CONVERT(datetime, 
  CONVERT(char(8), v.id_103))) &gt; - @number_days)
  ORDER ' is too long. Maximum length is 128.
  </code></pre>