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

Unless you're locked into using the proc_GetCol3_Value stored procedure, I would strongly suggest scrapping the cursor you are using, and turn your stored procedure into a user-defined function.

While I'm no fan of user-defined functions (due to performance issues), this might be a case to use one. You can most likely migrate your stored procedure code to a user-defined function (we'll call it func_GetCol3_Value) that returns the status, and now your query can simply be:

SELECT col1, col2, func_GetCol3_Value(col1, col2) as col3
FROM table1

Depending on how basic or complex your stored procedure is, you might not even need a function, but we would need to see the code first to tell.