This is an old revision of the document!


EXECUTE IMMEDIATE - Execute Dynamic SQL - Oracle to SQL Server Migration

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.

Database and SQL Migration Tools

About SQLines

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.

You could leave a comment if you were logged in.