SQL Stored Procedure : Incorrect syntax within imbricated IFs

Question

I get "Incorrect syntax" errors on every nested IF and ELSE statements in the following code... what's wrong ?

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

Try this:

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 2

Try not putting your begins and ends on the same lines as other statements.

Also, just as an unrelated tip, try using statements like "select @NbPartsReturned = count(*) from WTFAULT.PART..." rather than "set @NbPartsReturned = (select count(*) from WTFAULT.PART...", as you'll turn two statements into one.

If this doesn't take care of your problem, try putting up the text of the entire error.

Answer 3

I've changed the code to this and still the same errors :

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

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

DECLARE @NbFaultsInParts int,
  @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

SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO

Answer 4

Msg 102, Level 15, State 1, Procedure usp_WTFault_GetPartFaultStatus, Line 25

Incorrect syntax near ' '.

Msg 156, Level 15, State 1, Procedure usp_WTFault_GetPartFaultStatus, Line 29

Incorrect syntax near the keyword 'ELSE'.

Msg 102, Level 15, State 1, Procedure usp_WTFault_GetPartFaultStatus, Line 33

Incorrect syntax near ' '.

Msg 156, Level 15, State 1, Procedure usp_WTFault_GetPartFaultStatus, Line 37

Incorrect syntax near the keyword 'ELSE'.

Answer 5

Try this different method:

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

I also changed your query to use ANSI standard joins. You should use them too from now on. They are clearer, easier to maintain and will not give wrong results when you use outer joins and are far less likely to result in a cross join by accident.

Answer 6

Good call HLGEM, thanks !