Rewriting an Oracle procedure with cursor in a T-SQL procedure

Question

Hi,

I need to rewrite an Oracle procedure that uses heavily a cursor in a T-SQL stored procedure. An Oracle cursor is an object more efficient/performant than a T-SQL cursor and moreover the Oracle procedure logic works row by row and has several IF ... ELSE more hard to convert in T-SQL set-based operations.

Any ideas to solve a such issue? Thanks

Answer 1

SqlServer was based on the Sybase engine, where transact sql was a true language, and stored procedures are written in transact Sql. The language has cursors, if/then/else, loop, set, and can call other stored procs. you probably can migrate the code.

Sqlserver's CLR integration is more similar to Oracle's stored procedures:

https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/common-language-runtime-integration-overview?view=sql-server-ver16

you might be interested in writing stored procedures in python:

https://learn.microsoft.com/en-us/sql/machine-learning/sql-server-machine-learning-services?view=sql-server-ver16

Answer 2

With the amount of information you have shared, the only answer that can be given is: Roll up you sleeves and start working!

Rewriting procedural code into set-based is certainly not a walk in a park, as the mindsets are completely different. Those IF and ELSE typically translate to CASE expressions.

One tip: while you want to avoid cursors and loops, you don't need to write one big monolith query. You can split up the work by using intermediate temp tables. The important thing is that you work with all data at once and not one-by-one.