dzibus
asked on
Convert Oracle cursor to MSSQL cursor
How convert Oracle cursor to MSSQL.
DECLARE
CURSOR emp_cursor(p_deptno NUMBER) IS
SELECT empno, name
FROM emp
WHERE deptno=p_deptno;
v_emp_cursor emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor(10);
LOOP
FETCH emp_cursor INTO v_emp_cursor;
EXIT WHEN emp_cursor%NOTFOUND;
DELETE FROM emp WHERE empno=v_emp_cursor.empno;
END LOOP;
CLOSE emp_cursor;
commit;
END;
DECLARE
CURSOR emp_cursor(p_deptno NUMBER) IS
SELECT empno, name
FROM emp
WHERE deptno=p_deptno;
v_emp_cursor emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor(10);
LOOP
FETCH emp_cursor INTO v_emp_cursor;
EXIT WHEN emp_cursor%NOTFOUND;
DELETE FROM emp WHERE empno=v_emp_cursor.empno;
END LOOP;
CLOSE emp_cursor;
commit;
END;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DELETE Emp WHERE deptno = 10
or, to include the use of a variable for the parameter
Declare @p_deptno int
Select @p_deptno = 10
DELETE Emp WHERE deptno = @p_deptno