CURSOR WITH IN THE PROCEDURE

CREATE   PROC USP_CUR_EMP

AS

BEGIN

  CREATE TABLE  #TEMP(EMPNO INT, ENAME VARCHAR(50), SAL INT ,DNAME VARCHAR(50))

  DECLARE @EMPNO INT,@ENAME VARCHAR(50),@SAL INT

  DECLARE @DNAME VARCHAR(50)

  DECLARE @DNO INT

  DECLARE C1 CURSOR FOR SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP

  OPEN C1

    FETCH NEXT FROM C1 INTO @EMPNO,@ENAME,@SAL,@DNO

    WHILE(@@FETCH_STATUS=0)

      BEGIN

          INSERT INTO #TEMP(EMPNO,ENAME,SAL)VALUES( @EMPNO,@ENAME,@SAL)

          SELECT @DNAME=DNAME FROM DEPT WHERE DEPTNO=@DNO

UPDATE  #TEMP SET DNAME=@DNAME WHERE EMPNO=@EMPNO

FETCH NEXT FROM C1 INTO @EMPNO,@ENAME,@SAL,@DNO

      END

  CLOSE C1

  DEALLOCATE C1

  SELECT  *  FROM  #TEMP

END

EXEC USP_CUR_EMP

DROP TABLE  #TEMP

THE OUTPUT LIKE THIS

EMPNO      ENAME         SAL          DNAME

7369	SMITH	800	RESEARCH
7499	ALLEN	1600	SALES
7521	WARD	1250	SALES
7566	JONES	2975	RESEARCH
7654	MARTIN	1250	SALES
7698	BLAKE	1255	SALES
7782	CLARK	2450	ACCOUNTING
7788	SCOTT	3000	RESEARCH
7839	KING	5000	ACCOUNTING
7844	TURNER	1500	SALES