Stored Procedures
You can call a stored procedure from an SQL transformation. You can use a stored procedure to automate tasks in a relational database. Stored procedures accept user-defined variables, conditional statements, and other features that standard SQL statements do not support.
The SQL transformation connects to a relational database to run the stored procedure. The SQL transformation can call stored procedures from Oracle, IBM DB2, Microsoft SQL Server, Sybase, and ODBC. A stored procedure is kept in the database, and it runs in the database.
Create an ODBC connection to call a stored procedure from a Sybase database. You must also create an ODBC connection to call a stored procedure from a Microsoft SQL Server database on non-Windows operating systems.
A stored procedure is a pre-compiled collection of Transact-SQL, PL-SQL, or other database procedural statements. Stored procedure syntax varies based on the database.
You might use stored procedures to complete the following tasks:
- •Check the status of a target database before loading data into it.
- •Determine if enough space exists in a database.
- •Perform a specialized calculation.
- •Retrieve data by a value.
- •Drop and re-create indexes.
You can use a stored procedure to perform a query or calculation that you would otherwise include in a transformation. For example, if you have a well-tested stored procedure for calculating sales tax, you can perform that calculation with the stored procedure instead of recreating the same calculation in an Expression transformation.
A stored procedure can accept input and then return a result set of rows. A stored procedure can run a DDL task that requires no input and then returns no output.
You can configure the SQL transformation to run more than one stored procedure. For each stored procedure that you configure, configure transformation ports to match the stored procedure parameters. Each stored procedure can pass data back to output ports.
The database that contains the stored procedure has user permissions. You must have permissions to run the stored procedure on the database.
Note: A stored function is similar to a stored procedure, except that a function returns a single value. The SQL transformation can run stored functions.
SQL Transformation Ports for Stored Procedures
The SQL transformation input and output ports correspond to the input and output parameters in the stored procedure.
When you import a stored procedure, the Developer tool determines the database type from the database connection. It generates input and output ports in the SQL transformation from the parameters in the stored procedure. The Developer tool determines the native datatype of each port from the parameter of the stored procedure.
When you manually configure the SQL transformation, you need to configure the input and the output ports in the transformation. When you configure the database type, the SQL transformation changes the native datatype for each port based on the database type that you enter.
The following types of data can pass between the SQL transformation and the stored procedure:
- Input and output parameters
- The SQL transformation sends parameters to the stored procedure and the SQL transformation receives parameters from the stored procedure in the input and output ports.
- Return value
- If the stored procedure passes a return value, the Developer tool creates a Return Value port.
- SQL errors
- The SQL transformation returns errors from the stored procedure in the SQLError port.
Input and Output Parameters
When you call a stored procedure from an SQL transformation, each field that the call statement references identifies an input or output port. When you import a stored procedure, the Developer tool generates the stored procedure call statement. Otherwise, you need to manually configure the call statement.
You can edit the call statement in the SQL view of the transformation.
The call statement has the following format:
?RETURN_VALUE? = call <stored proc name>(?Field1?, ?Field2?,. . . )
Enclose the port names in question marks. The port names do not have to match the parameter names in the stored procedure. The output ports must be in the same order as parameters in a SELECT query.
You can use a stored procedure that contains INOUT parameters. The SQL transformation identifies INOUT parameters by the input port name. The output port has the prefix output_. The Data Integration service binds the input port and output port to the same parameter.
You can configure an SQL transformation to return a result set. When the stored procedure returns a result set, the Developer tool cannot create output ports for the columns in the result set. When you import the stored procedure, you must manually enter the ports and configure the stored procedure call.
Return Value
A return value is a code or text string that defines that status of the stored procedure. When the stored procedure has a return value, the SQL transformation has a Return Value port.
Most databases can pass a return value after running a stored procedure. The return value can contain an integer value, or it can contain a value that you define in the stored procedure. For example, a stored procedure might return "Success" when the procedure succeeds.
If a stored procedure returns a result set instead of a single return value, the SQL transformation receives the first return value from the procedure.
Stored Procedure Result Sets
You can configure a SQL transformation to receive a result set from a stored procedure. A stored procedure returns multiple rows in a result set. The SQL transformation can return each row to the mapping.
The following stored procedure returns a result set:
CREATE OR REPLACE FUNCTION fetchEMPinfo
(p_State IN VARCHAR2 )
return types.cursortype
AS
my_cursor types.cursortype;
BEGIN
OPEN my_cursor FOR SELECT EMP_ID, NAME, CITY FROM EMP WHERE STATE = p_State ORDER BY EMP_ID;
RETURN my_cursor;
END;
When you import the stored procedure, the Developer tool creates a stored procedure call statement similar to the following syntax:
call FETCHEMPINFO (?P_STATE?)
The input parameter is p_state. The Developer tool does not create the output ports for you. You must manually create the output ports with the same datatypes as the stored procedure parameters.
For example, the resultSet contains the EMP_ID, EMPNAME, and CITY columns. Create output ports for these columns.
You must also manually update the SQL call with the output columns using the following syntax:
(?EMP_ID?,?EMPNAME?,?CITY?) = call FETCHEMPINFO (?P_STATE?)
Result Sets with Different Databases
Configure stored procedures to return result sets using different syntax based on the database type.
Oracle
An Oracle stored function returns results with a cursor:
create or replace function sp_ListEmp return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;
return l_cursor;
end;
Oracle also accepts cursors as input parameters. You cannot configure cursors as input parameters with the SQL transformation.
Microsoft SQL Server
A Microsoft SQL Server stored procedure returns a result set stored procedure with a select statement in the procedure body or with the return type explicitly declared as a table.
Create PROCEDURE InOut(
@inout varchar(100) OUT
)
AS
BEGIN
set @inout = concat(@inout,'__')
select * from mytable;
END
IBM DB2
An IBM DB2 stored procedure can return a resultset with an open cursor. The number of result sets it returns are declared in the RESULT SET clause. The stored procedure opens a cursor and returns it. The below example returns 2 open cursors.
CREATE PROCEDURE TESTMULTIRS
(IN i_cmacct CHARACTER(5))
RESULT SETS 2
LANGUAGE SQL
BEGIN
DECLARE csnum INTEGER;
--Declare serial cursors to consume less resources
--You do not need a rollable cursor.
DECLARE getDeptNo CHAR(50); --Be careful with the estimated length.
DECLARE getDeptName CHAR(200);
DECLARE c1 CURSOR WITH RETURN FOR s1;
SET getDeptNo = 'SELECT DEPTNO FROM DEPT';
SET getDeptName = 'SELECT DEPTNAME FROM DEPT';
PREPARE s1 FROM getDeptNo;
OPEN c1;
END;
Sybase
A Sybase stored procedure returns a result set stored procedure with a select statement in the procedure body or with the return type explicitly declared as a table.
CREATE PROCEDURE FETCHEMPINFO
(
@p_State VARCHAR(5),
@e_id INT OUTPUT,
@e_name VARCHAR(50) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SELECT EMP_ID, NAME FROM EMP WHERE STATE = @p_State ORDER BY EMP_ID
SET NOCOUNT OFF
SELECT @e_id AS EMP_ID, @e_name AS NAME
RETURN
END
GO
--Configure the following variables to execute the procedure.
DECLARE @p_State VARCHAR(5)
DECLARE @EMPID int
DECLARE @EMPNAME varchar(50)
SET @p_State = 'CA'
exec FETCHEMPINFO @p_State, @e_id = @EMPID, @e_name = @EMPNAME
GO
Result Set Rows
Some stored procedures return output parameters in addition to the result set rows. The SQL transformation returns output parameters in the last row. It does not include the single-occurring output parameters in the result set rows.
For example, you write a stored procedure that receives an employee ID and returns the employee name in output parameter 1 and department in output parameter 2. The stored procedure also returns a row for each sick day that the employee took throughout the year. The row contains the date, the number of hours, and the reason for the absence.
The result set contains a different number of rows for each employee. Each row in the result set contains an empty employee name and department. The SQL transformation returns the employee name and department after the result set. The employee name and department appear in the last row.
Stored Procedure Example
You can call a stored procedure that returns data to the SQL transformation.
The following stored procedure receives an employee number and returns one row with the employee number and the employee name:
CREATE OR REPLACE PROCEDURE SP_GETNAME
(IN_EMPNO IN NUMBER, OUT_EMPNO NUMBER, OUT_NAME OUT STRING)
AS
BEGIN
SELECT EMP_KEY,EMP_NAME into OUT_EMPNO , OUT_NAME from EMP_TABLE where EMP_KEY=IN_EMPNO;
END;/"
To create the SQL transformation, import the stored procedure. The Developer tool creates the input ports and the output ports. The port names are the same as the parameter names in the stored procedure.
The following figure shows the ports for the SQL transformation:
The Developer tool creates the following stored procedure call to retrieve the employee name:
call SP_GETNAME (?IN_EMPNO?,?OUT_EMPNO?,?OUT_NAME?)
You can view the stored procedure call in the SQL Editor. Any SQL errors appear in the SQLError port.
Result Sets with Different Databases
Configure stored procedures to return result sets using different syntax based on the database type.
Oracle
An Oracle stored function returns results with a cursor:
create or replace function sp_ListEmp return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;
return l_cursor;
end;
Oracle also accepts cursors as input parameters. You cannot configure cursors as input parameters with the SQL transformation.
Microsoft SQL Server
A Microsoft SQL Server stored procedure returns a result set stored procedure with a select statement in the procedure body or with the return type explicitly declared as a table.
Create PROCEDURE InOut(
@inout varchar(100) OUT
)
AS
BEGIN
set @inout = concat(@inout,'__')
select * from mytable;
END
IBM DB2
An IBM DB2 stored procedure can return a resultset with an open cursor. The number of result sets it returns are declared in the RESULT SET clause. The stored procedure opens a cursor and returns it. The below example returns 2 open cursors.
CREATE PROCEDURE TESTMULTIRS
(IN i_cmacct CHARACTER(5))
RESULT SETS 2
LANGUAGE SQL
BEGIN
DECLARE csnum INTEGER;
--Declare serial cursors to consume less resources
--You do not need a rollable cursor.
DECLARE getDeptNo CHAR(50); --Be careful with the estimated length.
DECLARE getDeptName CHAR(200);
DECLARE c1 CURSOR WITH RETURN FOR s1;
SET getDeptNo = 'SELECT DEPTNO FROM DEPT';
SET getDeptName = 'SELECT DEPTNAME FROM DEPT';
PREPARE s1 FROM getDeptNo;
OPEN c1;
END;
Sybase
A Sybase stored procedure returns a result set stored procedure with a select statement in the procedure body or with the return type explicitly declared as a table.
CREATE PROCEDURE FETCHEMPINFO
(
@p_State VARCHAR(5),
@e_id INT OUTPUT,
@e_name VARCHAR(50) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SELECT EMP_ID, NAME FROM EMP WHERE STATE = @p_State ORDER BY EMP_ID
SET NOCOUNT OFF
SELECT @e_id AS EMP_ID, @e_name AS NAME
RETURN
END
GO
--Configure the following variables to execute the procedure.
DECLARE @p_State VARCHAR(5)
DECLARE @EMPID int
DECLARE @EMPNAME varchar(50)
SET @p_State = 'CA'
exec FETCHEMPINFO @p_State, @e_id = @EMPID, @e_name = @EMPNAME
GO
Result Set Rows
Some stored procedures return output parameters in addition to the result set rows. The SQL transformation returns output parameters in the last row. It does not include the single-occurring output parameters in the result set rows.
For example, you write a stored procedure that receives an employee ID and returns the employee name in output parameter 1 and department in output parameter 2. The stored procedure also returns a row for each sick day that the employee took throughout the year. The row contains the date, the number of hours, and the reason for the absence.
The result set contains a different number of rows for each employee. Each row in the result set contains an empty employee name and department. The SQL transformation returns the employee name and department after the result set. The employee name and department appear in the last row.
