CODEWITHSUNDEEP
t-sql
Gregy
Gregy

Reputation: 340

Procedure with loop

I would like to create procedure which set commision (COMM) for employees from given department number (DEPTNO) who has lower salary(SAL) than avarage SAL of DEPTNO

I have written following procedure:

CREATE PROCEDURE test1
    @deptno INT
AS
BEGIN
    DROP TABLE #emps
    DECLARE @avg INT
    SELECT @avg = AVG(SAL) FROM EMP WHERE DEPTNO = @deptno

    SELECT 
    RowNum = ROW_NUMBER() OVER(ORDER BY EMPNO)
    ,*
    INTO #emps
    FROM EMP

    DECLARE @MaxRownum INT
    SET @MaxRownum = (SELECT MAX(RowNum) FROM #emps)

    DECLARE @Iter INT
    SET @Iter = (SELECT MIN(RowNum) FROM #emps)

    WHILE @Iter <= @MaxRownum
    BEGIN
        SELECT *
        FROM #emps
        WHERE RowNum = @Iter

        SET COMM = SAL * 0,05 IF DEPTNO = @deptno AND SAL < @avg


        SET @Iter = @Iter + 1
    END
END

but it seems that it fails inside the loop. I have never used before any loops in SQL. How can I resolve this problem ?

Upvotes: 0

Views: 46

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

There's really no need for a loop here. With practice, you'll make the leap from row-by-row thinking to set thinking in SQL.

with cteDeptAvg AS (
    SELECT deptno, AVG(sal) as AverageSalary
        FROM emp
        GROUP BY deptno
)
UPDATE e
    SET comm = sal * 0.05
    FROM emp e
        INNER JOIN cteDeptAvg da
            ON e.deptno = da.deptno
    WHERE e.sal < da.AverageSalary
        AND e.deptno = @deptno;

Upvotes: 2

dfader2
dfader2

Reputation: 131

Failing how? Is it throwing an error or just not outputting the data that would would expect?

For one, you have COMM = SAL * 0,05, which I assume should be 0.05. That should fix the error unless that error is not actually in your code.

Generally when you are trying to iterate over a result set in SQL you want to use a cursor: http://www.tutorialspoint.com/plsql/plsql_cursors.htm

Upvotes: 2

Related Questions