In Oracle PL/SQL, you can use EXECUTE IMMEDIATE statement to execute a dynamic SQL statement.
In Microsoft SQL Server Transact-SQL, you can use EXECUTE statement or EXECUTE sp_executesql stored procedure to execute dynamic SQL statements.
Oracle PL/SQL:
DECLARE name VARCHAR2(70) := 'San Francisco'; sql_stmt VARCHAR2(100) := 'INSERT INTO cities (name) VALUES (:name)'; BEGIN EXECUTE IMMEDIATE sql_stmt USING name; END; /
SQL Server Transact-SQL:
You have to use EXECUTE sp_executesql if you need to execute a dynamic SQL with parameters.
BEGIN DECLARE @name VARCHAR(70) = 'San Francisco', @sql_stmt NVARCHAR(100) = 'INSERT INTO cities (name) VALUES (@name)'; EXECUTE sp_executesql @sql_stmt, N'@name VARCHAR(70)', @name; END; GO
Note sp_executesql requires first and second parameters (SQL statement and parameter format) to be NVARCHAR or NCHAR.
Also note that in a dynamic string you specify a parameter as :param in Oracle PL/SQL and @param in SQL Server Transact-SQL.
SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.
Written by Dmitry Tolpeko - February 2013.