Porting Stored Procedures Between Platforms

A procedure can compile on the new engine and still do the wrong work. Porting stored procedures is a behavior test, not a search-and-replace exercise. Error paths, temporary data, generated keys, and date rules need their own checks.

Describe the Contract Before Porting Stored Procedures

Before converting code, write what the procedure accepts, returns, changes, and promises under failure. Include transaction ownership, result sets, output parameters, and side effects such as audit rows or messages. Identify callers and their retry behavior. A translated procedure is correct only when those callers see the intended result. The parser does not sign the acceptance form.

I choose test inputs that follow the normal path and each important error path. What should remain committed after the third statement fails? If nobody can answer, the current procedure needs investigation before porting. A conversion tool can translate syntax, but it cannot decide the business meaning of a partial transaction.

Translate Error Handling Explicitly

PL/SQL exceptions, MySQL handlers, and T-SQL TRY and CATCH blocks do not behave identically. In SQL Server, inspect ERROR_NUMBER, ERROR_MESSAGE, and related functions inside CATCH. Decide whether to log, roll back, and rethrow. Use THROW when the caller must know the operation failed. Test nested procedures and transactions instead of assuming a catch block restores the connection to a clean state.

The sample below is isolated and shows the shape of a T-SQL error path. It catches a conversion error and returns its message. A production procedure needs a transaction policy and should not swallow the error unless the API promises that behavior. I check the state left behind after the failure, not just the text returned.

BEGIN TRY
    SELECT CONVERT(int, N'not-an-integer') AS Value;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Reconsider Temporary Tables When Porting Stored Procedures

When porting stored procedures, temporary tables can differ in scope, lifetime, indexing, and transaction behavior across platforms. SQL Server local temporary tables use a name beginning with # and live in tempdb for the session. A table variable has different optimizer and scope considerations. Choose based on row volume, indexes, reuse, and the procedure’s behavior. Do not translate every temporary structure into the same target object.

I inspect how the source procedure uses temporary data: one pass, multiple joins, or a handoff to nested code. Then I test the target with representative cardinality. The small example below creates and removes a local temporary table without touching user data. The cleanup matters in long-lived sessions and test scripts.

CREATE TABLE #PortDemo
(
    ItemId int NOT NULL PRIMARY KEY,
    Amount decimal(12,2) NOT NULL
);
INSERT INTO #PortDemo (ItemId, Amount)
VALUES (1, 12.50), (2, 18.75);
SELECT SUM(Amount) AS TotalAmount
FROM #PortDemo;
DROP TABLE #PortDemo;

Map Sequences and Identity Semantics

A source sequence can be shared across tables or called before a transaction. SQL Server has sequences and identity columns, but they are not interchangeable. Decide whether callers need the generated value before insert, whether gaps are acceptable, and whether several tables share one number stream. Do not replace a source sequence with IDENTITY just because both produce integers.

I check how the application retrieves the value. SQL Server OUTPUT inserted columns can return keys from an INSERT statement. SCOPE_IDENTITY can serve a narrower pattern. A global last identity assumption is unsafe under concurrent work. Test rollback and retry behavior because generated numbers need not be gap-free.

Rewrite Date Logic With a Time Model

Date functions, week boundaries, time zones, and implicit casts vary by engine. Decide whether each value is a date, a local wall-clock time, a UTC instant, or an offset-bearing timestamp. Then choose SQL Server date, datetime2, or datetimeoffset accordingly. Replace source-specific functions with explicit T-SQL expressions and test boundary dates.

The query shows DATEADD and DATEDIFF using fixed example inputs. It is a syntax demonstration, not a measured business result. I test month ends, leap days, and daylight saving behavior when the procedure depends on them. A function name that looks similar can still return a different answer.

SELECT
    DATEADD(day, 1, CONVERT(date, '20260131')) AS NextDay,
    DATEDIFF(day, CONVERT(date, '20260131'),
                  CONVERT(date, '20260202')) AS DaysBetween;

Check Null and String Rules

String concatenation, empty strings, collation, and null handling differ across platforms. A source database can treat an empty string differently from SQL Server. Review every branch that compares to an empty value or constructs a key. Use explicit casts where type precedence would otherwise decide the result. Test non-ASCII text and trailing spaces if the procedure joins or groups on strings.

I keep a comparison table of expected outputs for these cases. It is faster to discover a semantic mismatch in a tiny test than in a monthly report. Do not assume a successful port because the procedure returns the right row count on ordinary input.

Compare Plans and Permissions After Porting Stored Procedures

Translated code can have a different plan even when it returns the same rows. Check indexes, parameter patterns, cardinality, and temp table statistics under representative data. Measure runtime and reads on the target. Also test the procedure under its actual execution identity. Ownership chaining and permissions can differ from the source platform.

I separate correctness from tuning. First prove the results and side effects. Then inspect the slow paths. Changing logic and indexes at the same time can make a regression hard to explain. Keep a reproducible test case for every significant rewrite. Include expected rows, transaction state, and error behavior. Run the case after each subsequent schema change so a quiet dependency does not break the port.

Release With a Reversal Plan

Version the source procedure, target procedure, test cases, and deployment order together. If the application must change its calls, coordinate the release so old and new signatures do not collide. State the rollback point and data implications. A procedure that writes new-format data can make code rollback harder than redeploying a file.

The final review should compare normal outputs, error outputs, committed changes, and permissions. I ask another developer to run the test cases from the documented contract. That review catches assumptions the author no longer sees. Porting stored procedures is finished when the surrounding application can rely on the same meaning. Preserve a sample of source output and target output for each critical case, with sensitive values removed under policy.

Related reading on this blog: How is Oracle Temporary Table Different from SQL Server? Interview Question of the Week #133 and Dropping Temp Table in Stored Procedure: SQL in Sixty Seconds #124.

A ported procedure is not translated text, it is verified behavior under success and failure.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.