动态SQL中出错 [英] Error in Dynamic SQL

问题描述

我创建了以下SP以生成更新语句，它使用表
Col.TMap.T_Mp，表中的数据如下所示：

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            

我收到以下消息：

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.

输出应该是这样的：

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_CRAW_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]

推荐答案

您正在正确构建查询。所有的SQL部分都需要引号。他们之外唯一的东西应该是你所传递的变量。

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.

这篇关于动态SQL中出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！