We are converting DB2 procs over to SQL Server using the Microsoft SQL server migration assistant, and getting below error in the generated SQL Server proc:
Errors:DB22SS0245 The conversion of cursors in return statements is not supported
DB2 proc(omitting create syntax and actual complex logic):
BEGIN
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CALLER
FOR SELECT * FROM EMP
FOR READ ONLY;
OPEN temp_cursor;
END
How do I convert this to its SQL Server equivalent with the minimum amount of change? The possible solution I have read here suggest quite drastic changes to the proc, which is difficult for us since the procs are huge and many in number. Thank you!
Posting what worked for me. As the proc was just returning a select statement, the use of cursor does not add any advantage in this use case. Refactored the proc without cursor, and with just the select statment.
BEGIN
SELECT * FROM EMP
RETURN 0
END