Keyword 'ELSE' keeps causing incorrect syntax-CodePudding

I'm struggling with this else function.

I keep getting this error message specifically on Line 41:

Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'ELSE'

I understand other people have already asked about a similar issue. Except that in this case (Correct me if I'm wrong), I was told that some IF statements can still run without an ELSE statement at the end. The last ELSE statement is supposed to be the result of the first conditional not being true.

Here is my code:

USE PR;

DECLARE @TotHours decimal;
DECLARE @TotEmps int;
DECLARE @HolHours decimal;
DECLARE @RegHours decimal;
DECLARE @OTHours time;

SELECT @TotHours = SUM(WorkHours)
FROM Hours
WHERE PPID IN (SELECT MAX(PPID) FROM PayPeriod);

SELECT @TotEmps = COUNT(EmpID) FROM EmpData;

SELECT @HolHours = SUM(HolHours)
FROM Hours
WHERE PPID IN (SELECT MAX(PPID) FROM PayPeriod);

SET @RegHours = @TotEmps * 32;

IF @OTHours = @HolHours
BEGIN
    SELECT *
    FROM Hours           
    WHERE @OTHours > 0; 
END;

IF @RegHours > @TotHours
BEGIN
    SELECT *
    FROM Hours
    WHERE @OTHours = @HolHours AND @RegHours = @TotEmps * 32;

    PRINT 'Total regular hours for the week: '   CONVERT(varchar, @RegHours, 1);
    PRINT 'Total holiday hours for the week: '   CONVERT(varchar, @HolHours, 1);
    PRINT 'Total OT Hours for the week: '   CONVERT(varchar, @OTHours, 1);
END;
ELSE
BEGIN
    SELECT @TOtHours - MAX(@RegHours) AS OTHours
    FROM Hours;

    PRINT 'Total regular hours for the week: '   CONVERT(varchar, @RegHours, 1);
    PRINT 'Total holiday hours for the week: '   CONVERT(varchar, @HolHours, 1);
    PRINT 'Total OT Hours for the week: '   CONVERT(varchar, @OTHours, 1);
END;
ELSE
    PRINT 'This week had no holiday pay.';

CodePudding user response：

Your first END is in the wrong spot.

You want

USE PR;

DECLARE @TotHours decimal;
DECLARE @TotEmps int;
DECLARE @HolHours decimal;
DECLARE @RegHours decimal;
DECLARE @OTHours time;

SELECT @TotHours = SUM(WorkHours)
FROM Hours
WHERE PPID IN (SELECT MAX(PPID) FROM PayPeriod);

SELECT @TotEmps = COUNT(EmpID) FROM EmpData;

SELECT @HolHours = SUM(HolHours)
FROM Hours
WHERE PPID IN (SELECT MAX(PPID) FROM PayPeriod);

SET @RegHours = @TotEmps * 32;

IF @OTHours = @HolHours
BEGIN
    SELECT *
    FROM Hours           
    WHERE @OTHours > 0; 
    --not here

    IF @RegHours > @TotHours
    BEGIN
        SELECT *
        FROM Hours
        WHERE @OTHours = @HolHours AND @RegHours = @TotEmps * 32;

        PRINT 'Total regular hours for the week: '   CONVERT(varchar, @RegHours, 1);
        PRINT 'Total holiday hours for the week: '   CONVERT(varchar, @HolHours, 1);
        PRINT 'Total OT Hours for the week: '   CONVERT(varchar, @OTHours, 1);
    END;
    ELSE
    BEGIN
        SELECT @TOtHours - MAX(@RegHours) AS OTHours
        FROM Hours;

        PRINT 'Total regular hours for the week: '   CONVERT(varchar, @RegHours, 1);
        PRINT 'Total holiday hours for the week: '   CONVERT(varchar, @HolHours, 1);
        PRINT 'Total OT Hours for the week: '   CONVERT(varchar, @OTHours, 1);
    END;
END;-- HERE
ELSE
    PRINT 'This week had no holiday pay.';

Good code formatting helps to find this kind of error

CodePudding user response：

Problem in your IF ELSE END Statement You Not applied a IF ELSE Condition Properly