Developing Portable Applications : 4. Gateway Database Procedures : How to Register and Invoke Database Procedures with BYREF Parameters : Microsoft SQL Server BYREF Stored Procedure Example
 
Share this page                  
Microsoft SQL Server BYREF Stored Procedure Example
The following example shows how to create, register and invoke a database procedure with BYREF parameters 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 with BYREF parameters
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 order_byref;
\p\g

direct execute immediate 'drop procedure order_byref';
\p\g

direct execute immediate '
CREATE PROCEDURE order_byref
( @id_number integer OUTPUT,
  @name varchar(20) OUTPUT,
  @status char(20) OUTPUT,
  @order_date datetime OUTPUT )
     AS
       BEGIN
               INSERT INTO orders values ( @id_number, @name,
                                           @status, @order_date)
               SELECT @id_number=id_number, @name=name,
                      @status=status,   @order_date=order_date
                    FROM orders
                    WHERE orders.id_number=0
               DELETE from orders WHERE orders.id_number=0;
        END
';
2. Register the stored procedure as a gateway database procedure:
register procedure order_byref
    (  id_number       int  byref,
       name            varchar(20) byref,
       status          char(20) byref,
       order_date      date byref)
     as import
 from 'order_byref';
\p\g
3. Invoke the gateway database procedure (Embedded SQL code fragment):
col1=10;
sprintf(col2, "UpdateVarChar" );
sprintf(col3, "UpdateVarChar" );
sprintf(col4, "23-feb-2001" );
    
printf( "values before:\n col1: %d\n col2: %s\n col3: %s\n col4: %s\n\n",
col1, col2, col3, col4 );
EXEC SQL execute procedure order_byref
   (    id_number   = BYREF(:col1),
        name        = BYREF(:col2),
        status      = BYREF(:col3),
        order_date  = BYREF(:col4));
               
printf( "values after:\n col1: %d\n col2: %s\n col3: %s\n col4: %s\n\n",
col1, col2, col3, col4 );
After the execution of the procedure, the content of each parameter is different.