Creating a SQL stored procedure

Article
06/22/2012

When you add a custom XML node, you also have to create a SQL stored procedure for that node. You use the stored procedure to create, update, or delete a database record using the values in the data elements of the node. To associate the stored procedure with the node, the stored procedure and the custom node must have the same name.

For example, the previous section adds an <eConnectCustomProcedure> node to an XML document. To process the new node, you have to create a stored procedure named eConnectCustomProcedure. To process the data elements in the node, the stored procedure must include the following parameters:

You add an input parameter for each element of your custom node. For example, the <eConnectCustomProcedure> node requires you to add a CUSTNMBR parameter to the eConnectCustomProcedure stored procedure.
You add the input parameters in the same order that the element appears in the custom XML node.
To use the eConnect error handling process, you add output parameters that can hold an ErrorState value and an ErrString message. ErrorState specifies whether an error occurred and ErrString includes error codes and other information.

To add a stored procedure to your database server, refer to the SQL Server help documentation for information about installing SQL stored procedures.

The following SQL example shows a stored procedure for the <eConnectCustomProcedure> XML node. Notice that the eConnectCustomProcedure name matches the name of the node. Also notice how the input parameter named I_vCUSTNMBR maps to the CUSTNMBR element of the node. Finally, notice that the procedure includes output parameters named O_iErrorState and oErrString that enable the procedure to return error information.

/* Begin_Procs eConnectCustomProcedure */
if exists (select * from dbo.sysobjects where id =
    Object_id('dbo.eConnectCustomProcedure') and type = 'P')
begin
    drop proc dbo.eConnectCustomProcedure
end
go
create procedure dbo.eConnectCustomProcedure
@I_vCUSTNMBR char(15), /* Customer Number - only required field */
@O_iErrorState int output, /* Return value: 0 = No Errors, Any Errors > 0 */
@oErrString varchar(255) output /* Return Error Code List */
as
declare
    @CUSTBLNC int,
    @O_oErrorState int,
    @iError int,
    @iStatus smallint,@iAddCodeErrState int
/*********************** Initialize locals ******************************/
select
    @O_iErrorState = 0,
    @oErrString = '',
    @iStatus = 0,
    @iAddCodeErrState = 0
/***************** Custom Procedure edit check validation ***************/
/*If the @I_vCUSTNMBR variable is '' then we need to add the error code */
/*35010 to the @oErrString output variable.*/
/*The method that eConnect uses to append all error string is the */
/*taUpdateString procedure.*/
/*Error codes can be appended to the @oErrString variable: for example you */
/*could append a 33 44 55 66 to the @oErrString variable */
/*After the error codes have been appended to the @oErrString variable. */
/***********************************************************************/
if ( @I_vCUSTNMBR = '' )
begin
    select @O_iErrorState = 35010 /* Customer number is empty */
    exec @iStatus = taUpdateString
        @O_iErrorState,
        @oErrString,
        @oErrString output,
        @iAddCodeErrState output
end
/* Do some custom business logic */
select @CUSTBLNC = CUSTBLNC
    from RM00103 (nolock)
    where CUSTNMBR = @I_vCUSTNMBR
/* End custom business logic */
return (@O_iErrorState)
go
grant execute on dbo.eConnectCustomProcedure to DYNGRP
go

Creating a SQL stored procedure

Additional resources