Error in Dynamic SQL

I Have Created the following SP to generate the Update Statements and it uses the table Col.TMap.T_Mp and the data in the table looks like:

ID  M_Type  ID_F     SF1      SF2
1   Acc     ACC_ID   AC_ID    NULL
1   STA     STA_ID   ST_ID    NULL
1   CHa     Cha_ID   CH_ID    NULL



CREATE PROCEDURE dbo.dtmap(
@ID INT
)
AS
BEGIN

DECLARE @SQL NVARCHAR(MAX)
DECLARE @SchemaName SYSNAME
DECLARE @TableName SYSNAME
DECLARE @DatabaseName SYSNAME
DECLARE @M_Type SYSNAME
DECLARE @SF1 VARCHAR(50)
DECLARE @SF2 VARCHAR(50)
DECLARE @ID_F VARCHAR(50)
DECLARE @BR CHAR(2)
SET @BR = CHAR(13) + CHAR(10)


DECLARE tickmapcur CURSOR FOR SELECT M_Type,ID_F,SF1,SF2
                              FROM Col.TMap.T_Mp
                              WHERE   [_ID] = @ID 



SELECT  @SchemaName = Source_Schema ,
        @TableName = Source_Table ,
        @DatabaseName = Source_Database
FROM    Manserv.dbo.S_Ds
WHERE   ID = @ID



OPEN   tickmapcur  

FETCH NEXT FROM tickmapcur INTO @M_Type,@ID_F,@SF1,@SF2

while @@fetch_status = 0

BEGIN


SET @SQL = 'SELECT SO1,
                   SO2,
                   ID
INTO ' + @M_Type + '_mtemp' + @BR + 'FROM ' + @M_Type + @BR
    + 'WHERE [ID] = ' + CAST(@ID AS NVARCHAR(10)) + @BR+ @BR


SET @SQL = @SQL + 'UPDATE  mt ' + @BR + 'SET ' + @ID_F+ ' = ac.' + @ID_F
    + @BR + 'FROM ' + @DatabaseName + '.' + @SchemaName + '.' + @TableName + ' t' + @BR 
    + 'LEFT OUTER JOIN '+ @M_Type + '_mtemp' 
    +' mtemp ON mtemp.ID = ' +  CAST(@ID AS NVARCHAR(10)) + @BR 
     + 'INNER JOIN ' + @M_Type + ' ac ON t.' + @SF1
    + COALESCE(mtemp.SO1, '') + ' SV1'
    + CASE WHEN @SF2 is NULL THEN '' ELSE COALESCE(@SF2, '')
    + COALESCE(mtemp.SO2, '') + ' SV2' END
    + 'AND ac.[ID] = ' + CAST(@ID AS NVARCHAR(10))
    + @BR + 'INNER JOIN ' + @DatabaseName + '.' + @SchemaName + '.'
    + @TableName + '_Dmtemp mt' + @BR
    + 'ON mt.[SRID] = t.[RID]  ' + @BR
    +@BR

FETCH NEXT FROM tickmapcur INTO @M_Type,@ID_F,@SF1,@SF2

PRINT ( @SQL )
END 

CLOSE tickmapcur
DEALLOCATE tickmapcur

END            

I get the following message:

Msg 4104, Level 16, State 1, Procedure dtmap, Line 57
The multi-part identifier "mtemp.SO1" could not be bound.
Msg 4104, Level 16, State 1, Procedure  dtmap, Line 57
The multi-part identifier "mtemp.SO1" could not be bound.

The output should be like this:

SELECT SO1,
       SO2,
       ID
INTO  ACC_Mtemp
FROM ACC
WHERE [ID] = @ID



UPDATE  mt 
SET ACC_ID = ac.ACC_ID
FROM Ms.AT.AT_CRAW t
LEFT OUTER JOIN ACC_Mtemp mtemp ON
mtemp.ID = @ID
INNER JOIN Acc ac ON t.AC_ID=SV1
AND ac.ID = 1
INNER JOIN Ms.AT.AT_CR开发者_开发百科AW_Dmtemp mt  mt
ON mt.[SRID] = t.[RID]

SELECT SO1,
       SO2,
       ID
INTO  STA_Mtemp
FROM STA
WHERE [ID] = @ID



UPDATE  mt 
SET STA_ID = ac.STA_ID
FROM Ms.AT.AT_CRAW t
LEFT OUTER JOIN STA_Mtemp mtemp ON
mtemp.ID = @ID
INNER JOIN STA ac ON t.ST_ID=SV1
AND ac.ID = 1
INNER JOIN Ms.AT.AT_CRAW_Dmtemp mt  mt
ON mt.[SRID] = t.[RID]


SELECT SO1,
       SO2,
       ID
INTO  CHa_Mtemp
FROM CHa
WHERE [ID] = @ID



UPDATE  mt 
SET CHa_ID = ac.CHa_ID
FROM Ms.AT.AT_CRAW t
LEFT OUTER JOIN CHa_Mtemp mtemp ON
mtemp.ID = @ID
INNER JOIN CHa ac ON t.CH_ID=SV1
AND ac.ID = 1
INNER JOIN Ms.AT.AT_CRAW_Dmtemp mt  mt
ON mt.[SRID] = t.[RID]

You are building the query incorrectly. ALL of the SQL parts need to be in quotes. The only thing outside of them should be the variables you are passing in.

You can't use the mtemp alias while you are creating the string. I suppose that you want to use it in the query instead:

+ ' COALESCE(mtemp.SO1, '''') SV1'

Same for mtemp.SO2 two lines down.

Looks like your missing a single quote before the COALESCE(mtemp.SO1, '') section.