Microsoft SQL Server Stored Procedure Example
The following example shows how to create, register and invoke a database procedure 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 permimssions on the gateway procedure catalogs.
To create, register and invoke a database procedure
1. Create the Microsoft SQL Server stored procedure from the terminal monitor.
DIRECT EXECUTE IMMEDIATE 'DROP PROCEDURE mss_credit_update ';
DIRECT EXECUTE IMMEDIATE
'CREATE PROCEDURE mss_credit_update
(@acc_no integer,
@amount float )
AS
UPDATE accounts
SET balance = balance + @amount
WHERE account_id = @acc_no;';
2. Register the stored procedure as a gateway database procedure:
REMOVE PROCEDURE gwcredit_update;
REGISTER PROCEDURE gwcredit_update
(acc_no INTEGER4,
amount FLOAT8)
AS IMPORT
FROM 'mss_credit_update';
3. Invoke the gateway database procedure:
EXECUTE PROCEDURE gwcredit_update
(acc_no = 14567,
amount = 1678.93);