SQL Server Cursor To Include the Value in Select
I have a SELECT 开发者_StackOverflow中文版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.
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
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.
精彩评论