Creating a SQL stored procedure

When you add a custom XML node to an eConnect XML document, you must supply a SQL stored procedure to process that node. The previous section added the <eConnectCustomProcedure> node to an XML document. To process XML documents that contain this node, create a stored procedure with a name that exactly matches the name of the XML node. To continue the <eConnectCustomProcedure> example, the stored procedure must be named eConnectCustomProcedure.

The custom stored procedure’s parameters must include the following:

• The stored procedure must provide an input parameter for each element of the custom XML node.
• The stored procedure’s input parameters must match the order of the elements in the custom XML node.
• To comply with eConnect’s error handling process, include two output parameters. Name the output parameters ErrorState and ErrString.

  Refer to the SQL Server help documentation for information about creating and installing SQL stored procedures.

  The following SQL example creates a stored procedure for the <eConnectCustomProcedure> XML node. Notice how the procedure is named, the way the XML node’s data element maps to the input parameter, and the implementation of error handling:

  /* 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
  
  

  ---

  Documentation Feedback
  
  

  ---