CODEWITHSUNDEEP
sql-servert-sql
user3816325
user3816325

Reputation: 549

Select statement in cursor

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

Upvotes: 5

Views: 48377

Answers (3)

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

Try and avoid Cursors. As for your question, Yes it will work but you will end-up with multiple result-sets. Declare a Table Variable and insert into that table variable and select from the table variable after the loop ends. Please see below for usage example:

CREATE PROCEDURE usp_try
AS
BEGIN
    DECLARE @StartLoop INT
    DECLARE @EndLoop INT
    DECLARE @esal INT

    DECLARE @Result TABLE (dname NVARCHAR(50))
    DECLARE @InitResult TABLE (id INT IDENTITY(1, 1),
                         dname NVARCHAR(50),
                         ename NVARCHAR(50),
                         esal INT)

    INSERT INTO @InitResult
    SELECT ddname,
         e.ename,
         e.sal
    FROM dept AS d, emp AS e
    WHERE e.deptno = d.deptno

    SELECT @StartLoop = MIN(ID),
         @EndLoop = MAX(ID)
    FROM @InitResult

    WHILE @StartLoop <= @EndLoop
       BEGIN

          INSERT INTO @Result
          SELECT dname
          FROM @InitResult
          WHERE ID = @StartLoop

          SET @StartLoop = @StartLoop + 1
       END

    SELECT *
    FROM @Result
END

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

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

Upvotes: 9

Paul Lucaciu
Paul Lucaciu

Reputation: 134

Yes, you can do select statements inside the cursor.

Upvotes: 0

Related Questions