Question

在以下代码中，我在每个嵌套的IF和ELSE语句上都会出现“语法错误”错误。。。怎么了？

ALTER PROCEDURE [WTFAULT].[usp_WTFault_GetPartFaultStatus]
(
@linxPartId int = -1,
@faultStatus varchar(10) output
)
AS
BEGIN

    DECLARE @NbFaultsInParts int,
            @NbPartsReturned int

    SET @NbPartsReturned = (SELECT COUNT(*) FROM WTFAULT.PART, WTFAULT.FAULT WHERE WTFAULT.PART.FAULT_COID = WTFAULT.FAULT.COID 
                                AND WTFAULT.PART.LINX_PARTID = @linxPartId AND WTFAULT.FAULT.MATURITY =  ALLOCATED )

    IF @NbPartsReturned > 0 BEGIN
            SET @faultStatus =  ALLOCATED 
    END
    ELSE BEGIN
            SET @NbPartsReturned = (SELECT COUNT(*) FROM WTFAULT.PART, WTFAULT.FAULT WHERE WTFAULT.PART.FAULT_COID = WTFAULT.FAULT.COID 
                                        AND WTFAULT.PART.LINX_PARTID = @linxPartId AND WTFAULT.FAULT.MATURITY =  DRAFT )

            IF @NbPartsReturned > 0 BEGIN
                    SET @faultStatus =  DRAFT 
            END
            ELSE BEGIN
                SET @NbPartsReturned = (SELECT COUNT(*) FROM WTFAULT.PART, WTFAULT.FAULT WHERE WTFAULT.PART.FAULT_COID = WTFAULT.FAULT.COID 
                                            AND WTFAULT.PART.LINX_PARTID = @linxPartId AND WTFAULT.FAULT.MATURITY =  CLOSED )
                IF @NbPartsReturned > 0 BEGIN
                        SET @faultStatus =  CLOSED 
                END
                ELSE BEGIN
                        SET @faultStatus =   
                END
            END
        END
END
GO

Answer 1

尝试这种不同的方法：

ALTER PROCEDURE [WTFAULT].[usp_WTFault_GetPartFaultStatus](@linxPartId int = -1,
@faultStatus varchar(10) output)
AS   

SELECT COUNT(*) as MaturityCount,WTFAULT.FAULT.MATURITY  INTO #Temp
FROM WTFAULT.PART JOIN WTFAULT.FAULT 
    ON WTFAULT.PART.FAULT_COID = WTFAULT.FAULT.COID 
WHERE TFAULT.PART.LINX_PARTID = @linxPartId 
GROUP BY WTFAULT.FAULT.MATURITY

If (select MaturityCount from #temp where WTFAULT.FAULT.MATURITY =  ALLOCATED ) >0
    BEGIN
        SET @faultStatus =  ALLOCATED 
    END
ELSE IF
(select MaturityCount from #temp where WTFAULT.FAULT.MATURITY =  DRAFT ) >0
    BEGIN
        SET @faultStatus =  DRAFT 
    END
ELSE IF
(select MaturityCount from #temp where WTFAULT.FAULT.MATURITY =  CLOSED ) >0
    BEGIN
        SET @faultStatus =  CLOSED   
    END
ELSE 
    BEGIN                                        
        SET @faultStatus =                           
    END

我还将您的查询更改为使用ANSI标准联接。从现在起，你也应该使用它们。它们更清晰，更容易维护，并且当你使用外部联接时不会给出错误的结果，而且不太可能意外地导致交叉联接。

Answer 2

试试这个：

DECLARE @faultStatus nvarchar(20)
DECLARE @NbFaultsInParts int
DECLARE @NbPartsReturned int

SELECT @NbPartsReturned = COUNT(*) FROM WTFAULT.PART, WTFAULT.FAULT WHERE WTFAULT.PART.FAULT_COID = WTFAULT.FAULT.COID AND WTFAULT.PART.LINX_PARTID = @linxPartId AND WTFAULT.FAULT.MATURITY =  ALLOCATED 

IF @NbPartsReturned > 0 BEGIN
                SET @faultStatus =  ALLOCATED 
END
ELSE BEGIN
                SELECT @NbPartsReturned = COUNT(*) FROM WTFAULT.PART, WTFAULT.FAULT WHERE WTFAULT.PART.FAULT_COID = WTFAULT.FAULT.COID  AND WTFAULT.PART.LINX_PARTID = @linxPartId AND WTFAULT.FAULT.MATURITY =  DRAFT 

                IF @NbPartsReturned > 0 BEGIN
                                SET @faultStatus =  DRAFT 
                END
                ELSE BEGIN
                        SELECT @NbPartsReturned = COUNT(*) FROM WTFAULT.PART, WTFAULT.FAULT WHERE WTFAULT.PART.FAULT_COID = WTFAULT.FAULT.COID  AND WTFAULT.PART.LINX_PARTID = @linxPartId AND WTFAULT.FAULT.MATURITY =  CLOSED 

                        IF @NbPartsReturned > 0 BEGIN
                                        SET @faultStatus =  CLOSED 
                        END
                        ELSE BEGIN
                                        SET @faultStatus =   
                        END
                END
        END
END

GO

Answer 3

尽量不要将开始和结束与其他语句放在同一行。

此外，只是一个不相关的提示，尝试使用类似"select @NbPartsReturned = count(*) from WTFAULT.PART..."的语句，而不是"set @NbPartsReturned = (select count(*) from WTFAULT.PART..."，这样您就可以将两个语句合并为一个。

如果这不能解决你的问题，试着把整个错误的文本放出来。

Answer 4

消息102，级别15，状态1，过程usp_WTFault_GetPartFaultStatus，第25行

附近的语法不正确。

消息156，级别15，状态1，过程usp_WTFault_GetPartFaultStatus，第29行

关键字ELSE附近的语法不正确。

消息102，级别15，状态1，过程usp_WTFault_GetPartFaultStatus，第33行

附近的语法不正确。

消息156，级别15，状态1，过程usp_WTFault_GetPartFaultStatus，第37行

关键字ELSE附近的语法不正确。

友情链接