SQL Server Cursor To Include the Value in Select

Question

I have a SELECT statement returning a set of rows.

From each row, I need to get a value of one column and pass it to a Stored Procedure to get a value that I would need to supply for the set of rows itself.

For example:

DECLARE @col1 int
DECLARE @col2 int
DECLARE @col3 varchar(20)

DECLARE myCursor CURSOR FOR
SELECT col1, col2, col3
FROM table1
WHERE....

OPEN myCursor
FETCH NEXT FROM myCursor
INTO @col1, @col2

WHILE @@FETH_STATUS = 0
BEGIN
  SET @col3 = EXEC proc_GetCol3_Value @col1, @col2

  FETCH NEXT FROM myCursor
  INTO @col1, @col2
END

CLOSE myCursor
DEALLOCATE myCursor

Now, based from that, I want to return the rows as retrieved by the original cursor, plus the value retrieved from the Stored procedure executed to be column 3's value.

Say, the rows returned by the SQL cursor is:

col1  col2  col3
  1   5000
  2   5000
  3   2500
  4   2000

And what I need is that all columns have been supplied after running the cursor and the Stored Proc and the result set should be:

col1  col2  col3
  1   5000  APPROVED
  2   5000  REJECTED
  3   2500  CANCELLED
  4   2000  APPROVED

Any ideas welcome. Thanks.

P.S. I know that a lot would advice to simply using a JOIN statement but since the stored procedure to be executed is quite complex, making a join with the cursor SQL statement would be too complicated.

Answer 1

If for some reason you can't change to a function:

DECLARE @col1 int 
DECLARE @col2 int 
DECLARE @col3 varchar(20) 
DECLARE @Output (col1 int, col2 int, col3 varchar (20))

DECLARE myCursor CURSOR FOR 
    SELECT col1, col2, col3 FROM table1 WHERE....  
OPEN myCursor 
FETCH NEXT FROM myCursor INTO @col1, @col2  
WHILE @@FETH_STATUS = 0 
BEGIN   
    SET @col3 = EXEC proc_GetCol3_Value @col1, @col2

    INSERT INTO @Output (col1, col2, col3)
    VALUES @col1, @col2, @col3

FETCH NEXT FROM myCursor   INTO @col1, @col2 
END  
CLOSE myCursor 
DEALLOCATE myCursor 

SELECT col1, col2, col3 from @Output