Select statement in cursor

Question

Can we use select query within cursor.

Example:

CREATE PROCEDURE try  
AS  
            SET NOCOUNT ON  
Declare     @dname nvarchar(50),  
            @ename nvarchar(50),  
            @esal int


Declare curP cursor For

select d.dname,e.ename,e.sal from dept d,emp e where e.deptno=d.deptno

OPEN curP  
Fetch Next From curP Into @dname, @ename,@esal

While @@Fetch_Status = 0 Begin


select dname from curp          // this is it possible?i dont want to use print bcs i want                        to display data in tabular form  



Fetch Next From curP Into @dname,@ename,@esal

End -- End of Fetch

Close curP
Deallocate curP

Answer 1

If you want to do it with a cursor:

CREATE PROCEDURE try  
AS  
            SET NOCOUNT ON  
Declare     @dname nvarchar(50)


DECLARE curP CURSOR FOR

SELECT d.dname FROM dept d

OPEN curP  
FETCH NEXT FROM curP INTO @dname

WHILE @@Fetch_Status = 0 

BEGIN

    SELECT 0 AS OrderBy, @dname , '' AS sal
    union
    SELECT 1, 
           ename , 
           CONVERT(VARCHAR,sal) AS sal
    FROM   emp
    WHERE  deptno = @dname
    ORDER BY OrderBy

    FETCH NEXT FROM curP INTO @dname

End 

Close curP
Deallocate curP

The above will result to :

╔══════════╦═══╗
║ Tech     ║   ║
║ Vikrant  ║ 5 ║
║ Nimesh   ║ 2 ║
╚══════════╩═══╝  
╔══════════╦═══╗
║ Creative ║   ║
║ Memo     ║ 6 ║
║ Sabir    ║ 5 ║
╚══════════╩═══╝  

There would be another way thought:

;WITH DeptsCTE AS
(
    SELECT deptno,
           dname,
           ROW_NUMBER() OVER (ORDER BY deptno) AS rn
    FROM   dept
)
SELECT dname, 
       sal
FROM   
(
    SELECT D.rn,
           0 AS drn,
           dname, 
           '' AS sal
    FROM   DeptsCTE  D
    UNION
    SELECT D.rn,
           1 AS drn,
           ename , 
           CONVERT(VARCHAR,sal) AS sal
    FROM   emp E
           JOIN DeptsCTE D
               ON D.deptno = E.deptno 
) AS T    
ORDER BY rn,drn