存储过程内的'）'附近的语法不正确

I am currently receiving the following error when trying to run my dynamic sql below; is there something I'm missing that's causing this error to be thrown?....

我正在尝试在下面运行我的动态SQL时收到以下错误;有什么我错过了导致这个错误被抛出的东西吗?....

Msg 102, Level 15, State 1, Procedure calc, Line 32 Incorrect syntax near ')'.

消息102,级别15,状态1,过程计算,第32行')'附近的语法不正确。

    CREATE PROCEDURE calc(@pC INT, @Array1 varchar(500) ,@pT VARCHAR(15), @pTN VARCHAR(15), @pPK BIGINT, @pCI FLOAT, @pFK BIGINT)
        AS 
        BEGIN
        --calculate peer statistics for level of peer group   
        DECLARE @sMI FLOAT, @num25 FLOAT, @num75 FLOAT, @num25V FLOAT, @num75V FLOAT
        DECLARE @sMD FLOAT, @sME FLOAT, @sIQ FLOAT, @sSD FLOAT, @sMin FLOAT, @sMax FLOAT, @sDiff FLOAT
        DECLARE @sLK BIGINT, @sNL BIGINT, @sNI BIGINT
        DECLARE @sUNC NVARCHAR(5)
        DECLARE @SQL NVARCHAR(MAX)


SET @SQL =  'IF ('+@pFK+' = 0) 
                BEGIN
                    INSERT INTO '+@pTN+' (fldBA) VALUES (2); 
                END
            ELSE  
                BEGIN  
                    IF ((SELECT COUNT(*) FROM '+@pTN+' WHERE fldFPK = '+@pFK+' and fldPK='+@pPK+' and fldCIA='+@pCI+') > 0)
                        BEGIN           
                            UPDATE '+@pTN+' SET fldAVA = 0, fldPK = '+@pPK+', fldDTA = GETDATE(), fldCIA = '+@pCI+',
                            fldME = '+@sME+',fldMD = '+@sMD+',fldIQ = '+@sIQ+',fldSD = '+@sSD+', fldND = '+@pC+' + 1,
                            fldNI = '+@sNI+', fldNL = '+@sNL+', fldMin = '+@sMin+', fldMax = '+@sMax+',fldUNC = '+@sUNC+'
                            WHERE fldFPK = '+@pFK+' and fldPK = '+@pPK+' and fldCIAc = '+@pCI+'
                        END
                    ELSE
                        BEGIN               
                            INSERT INTO '+@pTN+' (fldFPK, fldAVA, fldPK, fldDTA, fldCIA,fldMean,fldMD,fldIQ,fldSD,fldND,fldNI,fldNL,fldMin,fldMax,fldUNC) VALUES('+@pFK+', 0, '+@pPK+', GETDATE(), 
                            '+@pCI+', '+@sME+', '+@sMD+','+@sIQ+','+@sSD+','+@pC+'+ 1, '+@sNI+', '+@sNL+', '+@sMin+', '+@sMax+', '+@sUNC+')
                        END
                END'

exec @SQL

1 个解决方案

#1

I copied and pasted the procedure in SQL Server Management Studio. You don't have any syntax error. You have just to close the statement CREATE PROCEDURE with an END, because you started it with a BEGIN. You can do that in this way:

我在SQL Server Management Studio中复制并粘贴了该过程。您没有任何语法错误。您只需要使用END关闭语句CREATE PROCEDURE,因为您使用BEGIN启动它。你可以这样做:

CREATE PROCEDURE calc(@pC INT, @Array1 varchar(500) ,@pT VARCHAR(15), @pTN VARCHAR(15), @pPK BIGINT, @pCI FLOAT, @pFK BIGINT)
    AS 
    BEGIN

    /* BODY OF PROCEDURE */

    SET @SQL = ''

    EXEC @SQL

    END