CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Kendall
Kendall

Reputation: 37

"Incorrect Syntax Near..." SQL Stored Proc

I'm trying to create a stored proc that indicates a pay raise for two types of employees. Those with above average salaries and those with lower than average salaries. Both fnHeadCount and fnAverageSalary are functions that I've created that are working and have worked in the past. I'm getting an error with the second part. It says incorrect syntax for the equal sign and the 1.4075.

Here is the code. I made the errors bold:

CREATE PROC RAISEA9
BEGIN
UPDATE tblEmployeeA9
SET AnnualSalary=ROUND(1.025*AnnualSalary,2)
SELECT dbo.fnHeadCount(JobTitle) 
FROM dbo.tblEmployeeA9
WHERE Active='Y'
HAVING (dbo.fnHeadCount(JobTitle)-1 >=2) AND (AnnualSalary > dbo.fnAverageSalary(JobTitle))
END

BEGIN
SET AnnualSalary=ROUND(1.0475*AnnualSalary,2)
SELECT dbo.fnHeadCount(JobTitle)
FROM dbo.tblEmployeeA9
WHERE Active='Y'
HAVING (dbo.fnHeadCount(JobTitle)-1 >=2) AND (AnnualSalary <= dbo.fnAverageSalary(JobTitle))
END

Upvotes: 1

Views: 2320

Answers (4)

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

CREATE PROC RAISEA9
BEGIN

    UPDATE tblEmployeeA9
    SET AnnualSalary=ROUND(1.025*AnnualSalary,2)
    -- SELECT HAS TO GO, otherwise Sql Server will update ALL records
    --SELECT dbo.fnHeadCount(JobTitle) 
    --FROM dbo.tblEmployeeA9
    WHERE Active='Y'
    --THERE ARE NO aggregate functions, so HAVING is replaced with AND
    --HAVING 
    and (dbo.fnHeadCount(JobTitle)-1 >=2) 
    AND (AnnualSalary > dbo.fnAverageSalary(JobTitle))
    -- not yet time for END
    -- END
    -- And no need for BEGIN either
    -- BEGIN
    -- There is UPDATE statement needed
    UPDATE tblEmployeeA9
    SET AnnualSalary = ROUND (1.0475 * AnnualSalary,2)
    -- Again select has no place in UPDATE statement
    --SELECT dbo.fnHeadCount(JobTitle)
    --FROM dbo.tblEmployeeA9
    WHERE Active='Y'
    -- And HAVING gives place to AND
    --HAVING 
    AND (dbo.fnHeadCount(JobTitle)-1 >=2) 
    AND (AnnualSalary <= dbo.fnAverageSalary(JobTitle))

END

UPDATE:

Additionally, while we are on the same page, as you strive to close the gap between the rich and not so rich, note that first update will raise average salary for second update. Only ordering of operations (AnnualSalary >, AnnualSalary <=) saves you from giving someone two raises. The two updates are essentially the same and could be rewritten easily:

SET AnnualSalary = ROUND(AnnualSalary *
                    CASE WHEN AnnualSalary > dbo.fnAverageSalary(JobTitle)
                         THEN 1.025
                         ELSE 1.0475
                         END
                    ,2)

Of course, AND (AnnualSalary <= dbo.fnAverageSalary(JobTitle)) and AND (AnnualSalary <= dbo.fnAverageSalary(JobTitle)) would be deleted.

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

In addition to @Nicola's answer, you should notice that your procedure executes 2 Update statements. Depending on how and what exactly the dbo.fnAverageSalary(JobTitle) and dbo.fnHeadCount(JobTitle) calculate (whether the AnnualSalary is used in the calculations or not and whether the changes that the first update statement does, affect the calculations of the second update), you may have some rows updated by both statements or by none of them. If you want such behaviour, fine. If not, you should try to make the changes in one Update statement, with something like this:

CREATE PROC RAISEA9
BEGIN

    UPDATE dbo.tblEmployeeA9
    SET AnnualSalary 
        = CASE WHEN (AnnualSalary > dbo.fnAverageSalary(JobTitle))
                 THEN ROUND(1.025 * AnnualSalary, 2)
               ELSE ROUND(1.0475 * AnnualSalary, 2)
          END
    WHERE Active='Y'
      AND (dbo.fnHeadCount(JobTitle)-1 >=2) 

END

Upvotes: 1

Randy
Randy

Reputation: 16677

seems like you are missing the UPDATE statement on the second block.

Upvotes: 1

barsju
barsju

Reputation: 4446

Looks like you forgot the UPDATE tablename...

Upvotes: 1

Related Questions