EXEC, EXECUTE Statement for Procedures - SQL Server to PostgreSQL Migration

In SQL Server, the EXEC (or EXECUTE) statement can be used to execute a stored procedure. In PostgreSQL, you can use the CALL statement.

SQL Server:

  -- Sample procedure
  CREATE PROCEDURE sp1 @param1 VARCHAR(30), @param2 VARCHAR(30)
  AS
    PRINT @param1 + ', ' + @param2
  GO 
 
  -- Executing the procedure
  EXEC sp1 'Hello', 'world'
  /* Result: Hello, world  */
 
  -- Using named parameters
  EXEC sp1 @param1 = 'Hello', @param2 = 'world'
  /* Result: Hello, world  */
 
  -- Named parameters can be specified in any order
  EXEC sp1 @param2 = 'world', @param1 = 'Hello'
  /* Result: Hello, world  */

PostgreSQL:

  -- Sample procedure
  CREATE OR REPLACE PROCEDURE sp1 (p_param1 VARCHAR(30), p_param2 VARCHAR(30))
  AS $$
  BEGIN
    RAISE NOTICE '%', p_param1 || ', ' || p_param2;
  END;
  $$ LANGUAGE plpgsql;
 
    -- Executing the procedure
  CALL sp1('Hello', 'world');
  /* Result: NOTICE:  Hello, world */
 
  -- Using named parameters
  CALL sp1(p_param1 => 'Hello', p_param2 => 'world');
  /* Result: NOTICE:  Hello, world */
 
  -- Named parameters can be specified in any order
  CALL sp1(p_param2 => 'world', p_param1 => 'Hello');
  /* Result: NOTICE:  Hello, world */

In SQL Server, a stored procedure that does not have parameters can be invoked as EXEC sp_name:

SQL Server:

  -- Sample procedure without parameters
  CREATE PROCEDURE sp2
  AS
    PRINT 'Hello, world!'
  GO 
 
  -- Executing the procedure
  EXEC sp2
  /* Hello, world!  */

PostgreSQL requires () to call a procedure without parameters:

PostgreSQL:

  -- Sample procedure without parameters
  CREATE OR REPLACE PROCEDURE sp2()
  AS $$
  BEGIN
    RAISE NOTICE '%', 'Hello, world!';
  END;
  $$ LANGUAGE plpgsql;
 
  -- Executing the procedure
  CALL sp2();
  /* Hello, world!  */

For more information, see SQL Server to PostgreSQL Migration.