Dynamic SQL on Babelfish 4.8.0: Every Pattern Tested

Dynamic SQL is one of the most heavily used features in real-world SQL Server applications. Stored procedures that build queries at runtime, parameterised searches, dynamic pivot tables, and flexible reporting queries all rely on it. If you are migrating from SQL Server to Babelfish, you need to know that these patterns work before you commit to the move.

This post documents six dynamic SQL patterns tested against Babelfish 4.8.0 on PostgreSQL 16.11, running as a FoundryDB managed service. Every test passed without modification.

Test Setup

• Babelfish 4.8.0 on PostgreSQL 16.11
• Connected via sqlcmd on port 1433
• All queries run against a fresh instance with a single test table

CREATE TABLE dbo.products (
    id         INT IDENTITY(1,1) PRIMARY KEY,
    name       NVARCHAR(100) NOT NULL,
    category   NVARCHAR(50)  NOT NULL,
    price      DECIMAL(10,2) NOT NULL,
    stock_qty  INT           NOT NULL DEFAULT 0
);

INSERT INTO dbo.products (name, category, price, stock_qty) VALUES
    ('Widget A',   'hardware',   29.99, 150),
    ('Widget B',   'hardware',   49.99, 80),
    ('Gadget X',   'electronics', 199.99, 25),
    ('Gadget Y',   'electronics', 349.99, 10),
    ('Tool Alpha', 'tools',      89.99, 45);

1. sp_executesql with Input Parameters

The most common dynamic SQL pattern in SQL Server codebases. sp_executesql accepts a query string, a parameter definition string, and parameter values. This avoids string concatenation and protects against SQL injection.

DECLARE @sql   NVARCHAR(500);
DECLARE @cat   NVARCHAR(50) = 'hardware';
DECLARE @min   DECIMAL(10,2) = 20.00;

SET @sql = N'SELECT id, name, price FROM dbo.products
             WHERE category = @category AND price >= @minPrice';

EXEC sp_executesql @sql,
    N'@category NVARCHAR(50), @minPrice DECIMAL(10,2)',
    @category = @cat,
    @minPrice = @min;

Result: Returns Widget A (29.99) and Widget B (49.99). Passed.

2. sp_executesql with OUTPUT Parameter

OUTPUT parameters let you capture scalar results from dynamic SQL back into local variables. This is common in stored procedures that need to fetch a count, a max value, or a single ID dynamically.

DECLARE @sql      NVARCHAR(500);
DECLARE @cat      NVARCHAR(50) = 'electronics';
DECLARE @result   INT;

SET @sql = N'SELECT @cnt = COUNT(*) FROM dbo.products WHERE category = @category';

EXEC sp_executesql @sql,
    N'@category NVARCHAR(50), @cnt INT OUTPUT',
    @category = @cat,
    @cnt = @result OUTPUT;

SELECT @result AS electronics_count;

Result: Returns 2. Passed.

This is a pattern that some alternative PostgreSQL compatibility layers struggle with. Babelfish handles it correctly, including the bidirectional binding of the OUTPUT variable.

3. EXEC with String Concatenation

The older (and less safe) form of dynamic SQL. Many legacy SQL Server applications use EXEC() with concatenated strings instead of sp_executesql. You will encounter this in codebases that predate SQL Server 2005.

DECLARE @table NVARCHAR(100) = 'dbo.products';
DECLARE @sql   NVARCHAR(500);

SET @sql = 'SELECT TOP 3 name, price FROM ' + @table + ' ORDER BY price DESC';

EXEC(@sql);

Result: Returns Gadget Y (349.99), Gadget X (199.99), Tool Alpha (89.99). Passed.

While sp_executesql should be preferred for new code, you should not have to rewrite every legacy EXEC() call during migration. Babelfish supports both.

4. Dynamic Column Selection

Building the column list dynamically is common in reporting and export procedures that let users choose which fields to include.

DECLARE @cols NVARCHAR(500) = 'name, price, stock_qty';
DECLARE @sql  NVARCHAR(500);

SET @sql = 'SELECT ' + @cols + ' FROM dbo.products WHERE stock_qty > 40';

EXEC(@sql);

Result: Returns Widget A, Widget B, and Tool Alpha with all three columns. Passed.

5. Dynamic WHERE Clause Building

Search procedures that conditionally add filters based on which parameters the caller provides. This pattern appears in almost every SQL Server application with a search or list endpoint.

DECLARE @sql     NVARCHAR(1000);
DECLARE @cat     NVARCHAR(50)   = NULL;
DECLARE @minQty  INT            = 30;

SET @sql = 'SELECT name, category, stock_qty FROM dbo.products WHERE 1=1';

IF @cat IS NOT NULL
    SET @sql = @sql + ' AND category = ''' + @cat + '''';

IF @minQty IS NOT NULL
    SET @sql = @sql + ' AND stock_qty >= ' + CAST(@minQty AS NVARCHAR(10));

SET @sql = @sql + ' ORDER BY stock_qty DESC';

EXEC(@sql);

Result: Returns Widget A (150), Widget B (80), and Tool Alpha (45). The NULL category filter is correctly skipped. Passed.

In production, you would combine this pattern with sp_executesql parameters instead of string concatenation. The point here is that the conditional building logic itself works correctly on Babelfish.

6. sp_executesql with Dynamic Table Name

Table names cannot be passed as parameters to sp_executesql (this is a SQL Server limitation, not a Babelfish one). The standard workaround is to concatenate the table name into the SQL string while still parameterising the filter values.

DECLARE @table  NVARCHAR(100)  = 'dbo.products';
DECLARE @sql    NVARCHAR(500);
DECLARE @min    DECIMAL(10,2)  = 100.00;

SET @sql = N'SELECT name, price FROM ' + @table +
           N' WHERE price > @minPrice ORDER BY price';

EXEC sp_executesql @sql,
    N'@minPrice DECIMAL(10,2)',
    @minPrice = @min;

Result: Returns Gadget X (199.99) and Gadget Y (349.99). Passed.

This hybrid approach (concatenation for identifiers, parameters for values) is the recommended pattern on both SQL Server and Babelfish.

Why This Matters for Migration

Dynamic SQL is not an edge case. A 2019 analysis of SQL Server workloads found that over 60% of production stored procedures use some form of dynamic SQL. If your migration target does not support these patterns, you face a rewrite of core application logic.

Babelfish 4.8.0 supports all six patterns tested here:

Pattern	Method	Status
Parameterised query	sp_executesql with inputs	Passed
Scalar output capture	sp_executesql with OUTPUT	Passed
String concatenation	EXEC()	Passed
Dynamic column list	EXEC() with column variables	Passed
Conditional WHERE	Runtime filter building	Passed
Mixed identifiers and parameters	sp_executesql + concatenation	Passed

No syntax changes were required. The same T-SQL that runs on SQL Server runs on Babelfish without modification.

Get Started

Provision a Babelfish instance on FoundryDB. Your SQL Server application connects on port 1433 with the same drivers and dynamic SQL patterns it already uses. No rewrites required.