challenges

Google Cloud has introduced its Database Migration Service, which focuses on converting SQL Server stored procedures with multiple result sets into PostgreSQL code. The service uses an automated decision-making process to determine if a SQL Server procedure should be translated into a PostgreSQL stored procedure or function, based on the number of result sets and the presence of a scalar return value. Procedures with a single result set or scalar return value are converted into PostgreSQL stored procedures, while those with multiple result sets are transformed into functions returning a SETOF refcursor. In a healthcare reporting example, a master procedure retrieves patient details and may return multiple result sets along with a status integer. The PostgreSQL translation involves simpler child procedures as standard procedures and more complex routines as functions that open cursors sequentially. Scalar return values are handled by placing them in a dedicated cursor at the end of execution, changing how applications interact with the outputs. Testing of these translated objects must occur within an explicit transaction block due to PostgreSQL cursor lifecycle constraints. The user executes the function to generate cursor references and fetches data sequentially. Google Cloud's internal analysis classifies stored procedures by scanning for direct result sets and considers conditional logic and loops that may complicate result set counts. A directed graph of procedure calls is constructed to determine the result set classification. Database migrations often face challenges due to legacy application logic, especially with stored procedures designed to minimize database round trips. Successful code conversion is only part of the task, as teams must also adjust test harnesses and application data access layers for PostgreSQL's cursor management. Google Cloud's service categorizes SQL Server procedures into three types: no result sets, a single result set, or multiple/dynamic result sets.