Microsoft SQL Server Row Returning Database Procedure Example
The following example shows how to create, register and invoke a database procedure that returns rows using the Microsoft SQL Server gateway.
Note: The user creating the Microsoft SQL Server stored procedure must have create procedure permissions. The register procedure requires insert permissions on the gateway procedure catalogs. The execute procedure statement requires execute permissions for the Microsoft SQL Server stored procedure and select permissions on the gateway procedure catalogs.
To create, register and invoke a database procedure that returns rows
1. Create the Microsoft SQL Server stored procedure from the terminal monitor. The trailing spaces after each line are required to successfully create the procedure.
REMOVE PROCEDURE emp_rowproc;
\p\g
DIRECT EXECUTE IMMEDIATE 'DROP FUNCTION emp_rowproc';
\p\g
DIRECT EXECUTE IMMEDIATE '
CREATE FUNCTION emp_rowproc(@insalary float, @multiplier integer)
RETURNS TABLE
AS
RETURN (SELECT NAME, SALARY*@multiplier as SALARY, DEPT,
DIV, MGR, BIRTHDATE, NUM_DEP
FROM emp_rowproc_table WHERE SALARY <= @insalary)
';
\p\g
2. Register the stored procedure as a gateway database procedure:
REGISTER PROCEDURE emp_rowproc
(sal float,
multiplier integer)
AS IMPORT FROM 'emp_rowproc'
RESULT ROW
(char(10), float, char(8), char(3), char(10), char(25), integer);
\p\g
3. Invoke the gateway database procedure - Embedded SQL:
printf( "name Salary Dept Div Manager Hire date Department#\n");
EXEC SQL execute procedure emp_rowproc(multiplier = :multi,sal = 30000)
result row (:name, :fsalary, :cdept, :div, :mgr, :bdate,:num_dep);
EXEC SQL begin;
printf( "%s %8.2f %s %s %s %s %d\n",
name, fsalary, cdept, div, mgr, bdate, num_dep);
EXEC SQL end;
Last modified date: 08/22/2022