SQL Stored Procedure : Incorrect syntax within imbricated IFs

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

From stackoverflow

• 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
  

• 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.

• 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

• 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'.

• 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.

• Good call HLGEM, thanks !