Adding a SQL stored procedure for create and update operations
The Dynamics GP Service framework uses eConnect SQL stored procedures to insert or update a document in the Dynamics GP database. If your service uses the Create, Update, or Void operations of the Dynamics GP Service framework, you need to add SQL stored procedures for that operation.
In addition, eConnect SQL stored procedures validate data and implement the business logic associated with the document. The following diagram shows the typical structure of an eConnect SQL stored procedure that creates or updates a document.
.gif "Dd996509.WS_SQLProcStructureOnline(en-us,MSDN.10).gif")
When you add your document type to eConnect, you need to create a SQL stored procedure that performs each of these steps.
- Specify the input parameters. The input parameters define the data fields that the SQL stored procedure uses to create, update, or delete a document. In addition, you need to add the output parameters required by the eConnect error reporting system.
- Validate the input parameters. Verify that the required data fields for your document contain data values.
- Check whether the specified document exists. Use the result to determine whether your SQL stored procedure can perform the specified operation.
- Perform business logic associated with the document. Business logic often includes validating input parameter values, retrieving additional data, and performing calculation to produce new data values.
- Determine whether to lock a record during an update. To obtain exclusive access to a record, implement Dynamics GP active locking in your SQL stored procedure. For more information about active locking, see the Microsoft Dexterity documentation.
- Use the eConnect error reporting system to report errors. If your SQL stored procedure encounters validation or business logic errors, use the eConnect error reporting system to notify the caller of the error. For more information about adding error codes and messages, see Adding Error Codes.
To complete a create, update, or delete operation, the Dynamics GP Service framework sends an XML document to eConnect. The XML document specifies the name of the SQL stored procedure and contains XML nodes that supply data for the input parameters of the stored procedure.
To add a new document type to the Dynamics GP Service platform, you need to add SQL stored procedures that create, and update documents in the Dynamics GP database. One SQL stored procedure can be used to perform both the create and update operations for your document type. To create a SQL stored procedure that performs the create and update operations, complete the following steps:
Create a Visual Studio solution
Open Visual Studio. In the File menu, point to New, and then click Project. In the New Project window, expand Other Project Types, and then select Database from the list of Project types.
Enter a name for your project. Review the Location and Solution Name, and then click OK.
If you are prompted to add a database reference, add a reference to your Dynamics GP data server, and then click OK.
Add a SQL script file
From the Project menu, choose Add SQL Script. In the Add New Item window, click SQL Script in the Templates list. Enter a name for your script file, and then click Add.
Add SQL statements to create and define the stored procedure
Add SQL statements that specify the name of the stored procedure, add it to the stored procedures of a Dynamics GP company database, and define the input and output parameters of the stored procedure.
The following sample shows how to create a stored procedure named sampleLeadCreateUpdate. This stored procedure is used when eConnect performs create or update operations for the IG_Lead_MSTR table (IG001).
Notice the naming convention used with the input parameters. The name of each input parameter specifies a data field from the IG001 table. In addition, each input parameter name starts with "@I_v". The use of this naming convention makes it easier to see how the input parameters are used in the stored procedure. While not required, you should adopt a similar convention that makes it easier to update and maintain your stored procedure.
Also notice how each parameter specifies the data type and size for that parameter. When you specify input parameters, use the same type and size as the data field in the Dynamics GP database.
In this example, the parameters designated as /*<Required>*/ identify parameters that must contain data. Required parameters do not have a default value. The other parameters are optional parameters. Optional parameters must have a default value. Set the default to a value that allows the document to be successfully created or updated in the Dynamics GP database.
The parameters also include two output parameters named @O\_iErrorState and @oErrString. These parameters are used in eConnect error reporting and must be included with every eConnect business object. The type and size of each output parameter is also required.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P'
AND name = 'sampleLeadCreateUpdate')
BEGIN
DROP Procedure dbo.sampleLeadCreateUpdate
END
GO
CREATE Procedure dbo.sampleLeadCreateUpdate
(
@I_vLeadID char(15), /*<Required>*/
@I_vLeadName char(31), /*<Required>*/
@I_vSLPRSNID char(15), /*<Required>*/
@I_vCITY char(35) = '',
@I_vSTATE char(29) = '',
@I_vZIP char(11) = '',
@I_vADDRESS1 char(61) = '',
@I_vADDRESS2 char(61) = '',
@I_vPHONE1 char(21) = '',
@I_vPHONE2 char(21) = '',
@I_vFAX char(21) = '',
@I_vLeadBusinessCategory smallint, /*<Required>*/
@I_vCOUNTRY char(61) = '',
@I_vCONTACT char(61) = '',
@I_vPotentialRevenue numeric(19,5) = 0,
@I_vQualifiedLead smallint = 1,
@I_vLeadSource char(51) = '',
@I_vQualificationDate datetime = '',
@O_iErrorState int OUTPUT,
@oErrString varchar(255) OUTPUT
)
AS
set transaction isolation level read uncommitted
set nocount on
Add SQL statements to declare and initialize local variables
The stored procedure for a create/update operation requires four local variables that are used to manage status and error state information. The code in the stored procedure must initialize the local variables.
The following script sample shows how the required local variables for the create/update stored procedure are defined and initialized. The sample also shows how the @oErrString parameter is initialized.
/** Declare local variables **/
declare
@iStatus int,
@iAddCodeErrState int,
@exists tinyint,
@O_oErrorState int,
/** Initialize local variables **/
select @O_iErrorState = 0,
@iStatus = 0,
@exists = 0,
@O_oErrorState = 0,
if (@oErrString is NULL)
begin
select @oErrString = ''
end
Add SQL statements to validate the input parameters
The stored procedures should contain code to verify that the input parameters contain valid data.
The following script sample shows how to validate input parameters. Notice how a value is assigned to the @O\_iErrorState output parameter after each validation error.
Hint: The eConnect error reporting system uses this parameter value to retrieve the error message from the taErrorCode table of the the DYNAMICS (system) database.
For more information about how to add error codes, see Adding Error Codes.
/** Verify there are no null required parameters **/
if(@I_vLeadId is null or
@I_vLeadName is null or
@I_vSLPRSNID is null or
@I_vLeadBusinessCategory is null
)
begin
select @O_iErrorState = 61061 /* A required parameter was null */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@O_oErrorState output
return (@O_iErrorState)
end
/** Verify there are no invalid negative amounts **/
if (@I_vPotentialRevenue < 0)
begin
select @O_iErrorState = 61062 /* Potential Revenue value is not valid */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@O_oErrorState output
return (@O_iErrorState)
end
Determine whether the record exists
Determine whether the SQL stored procedure should create a new record or update an existing record. The following script sample shows how to use a query to identify whether the specified record exists.
/** Determine whether the specified record exists **/
/* If the specified lead exists, set the @exists local variable to 1 */
if(exists(select top 1 LeadID from IG001 (nolock) where LeadID = @I_vLeadID))
begin
select @exists = 1
end
Implement the business logic
Implement the business logic for the document and the operation. Business logic ranges from simply validating parameters to performing additional calculations required by the document.
The following script sample implements business logic associated with a lead document. In this example, the business logic validates several of the data values for the lead being created or updated. If a validation error occurs, the appropriate error code is assigned to the @O\_iErrorState output parameter, the stored procedure ends, and the output parameters are returned to the caller.
/* Verify the required ID field is populated */
if(@I_vLeadID = '')
begin
select @O_iErrorState = 61063 /* A Lead ID value was not supplied */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@O_oErrorState output
return (@O_iErrorState)
end
/* Verify the required Name field is populated */
if(@I_vLeadName = '')
begin
select @O_iErrorState = 61064 /* The Name value was not supplied */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@O_oErrorState output
return (@O_iErrorState)
end
/* Verify the required Salesperson ID field is populated */
if(@I_vSLPRSNID = '')
begin
select @O_iErrorState = 61065 /* The Salesperson was not specified */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@O_oErrorState output
return (@O_iErrorState)
end
/* Validate that the salesperson ID exists */
/* Verify the salesperson ID is in the RM00301 table of the company */
if(not exists(select top 1 SLPRSNID from RM00301 (nolock) where SLPRSNID = @I_vSLPRSNID))
begin
select @O_iErrorState = 61066 /* The salesperson does not exist */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@O_oErrorState output
return (@O_iErrorState)
end
/* Validate the LeadBusinessCategory value */
if((@I_vLeadBusinessCategory < 1) or (@I_vLeadBusinessCategory > 8))
begin
select @O_iErrorState = 61067 /* The Business Category is not valid */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@O_oErrorState output
return (@O_iErrorState)
end
/* Validate the qualified lead value */
if((@I_vQualifiedLead < 1) or (@I_vQualifiedLead >2))
begin
select @O_iErrorState = 61068 /* Qualified property is not valid */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@O_oErrorState output
return (@O_iErrorState)
end
/* Validate that Lead Source and Qualification date are only supplied */
/* when the Qualified Lead value is true */
if(@I_vQualifiedLead = 1)
begin
if((@I_vLeadSource <> '') or (@I_vQualificationDate <> ''))
begin
select @O_iErrorState = 61069 /* Source and Qualification */
/* cannot be set when the Qualified field is false */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@O_oErrorState output
return (@O_iErrorState)
end
end
Add SQL statements to create a new record
The stored procedure must contain code to create a new record in the Dynamics GP database. Use the value of the @exists variable that you set earlier to determine whether to create a record in the Dynamics GP database. If @exists is false (0), insert a new record in the database. Use the input parameters and variables to supply data values for each data field of the new record.
The following script sample shows how to check the value of the @exists variable and how to add a SQL insert statement that creates a lead.
/** If the specified record does not exist, insert the record in IG001 **/if(@exists = 0)
begin
insert IG001
(
LeadID,
LeadName,
SLPRSNID,
CITY,
STATE,
ZIP,
ADDRESS1,
ADDRESS2,
PHONE1,
PHONE2,
FAX,
LeadBusinessCategory,
COUNTRY,
CONTACT,
PotentialRevenue,
QualifiedLead,
LeadSource,
QualificationDate,
Workflow_Approval_Status,
Workflow_Priority,
Approved_Salesperson_ID
)
select
@I_vLeadID,
@I_vLeadName,
@I_vSLPRSNID,
@I_vCITY,
@I_vSTATE,
@I_vZIP,
@I_vADDRESS1,
@I_vADDRESS2,
@I_vPHONE1,
@I_vPHONE2,
@I_vFAX,
@I_vLeadBusinessCategory,
@I_vCOUNTRY,
@I_vCONTACT,
@I_vPotentialRevenue,
@I_vQualifiedLead,
@I_vLeadSource,
@I_vQualificationDate,
@WorkflowApprovalStatus,
@WorkflowPriority,
@ApprovedSalesperson
if(@@error <> 0)
begin
select @O_iErrorState = 61070 /* An insert error occurred */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@iAddCodeErrState output
return (@O_iErrorState)
end
end
Lock the record (optional)
If your business object updates a record in a table that supports active locking, use a lock to obtain exclusive access to the record. To use locking, add a local variable and initialize it to zero.
@lock tinyint @lock = 0
The eConnect business object implement active locking by calling a stored procedure named taDEXLOCKS. To use taDEXLOCKS, set the @_vOperation parameter to "1", specify the table name, and row ID of the record to lock. The following script sample locks a purchase order.
/* Get the table and row data for taDEXLOCKS */
select @POPHdrTbl = rtrim(db_name()) + '.dbo.POP10100'
select @DexRowID = DEX_ROW_ID from POP10100 (nolock) where PONUMBER = @I_vPONUMBER
/* Check for an existing lock */
if (not exists(select 1 from tempdb..DEX_LOCK (nolock) where
table_path_name = @POPHdrTbl and row_id = @DexRowID
and session_id = @@spid))
begin
/* If the record is not locked, set a new lock */
select @lock = 1
exec @iStatus = DYNAMICS..taDEXLOCKS
@I_vOperation = 1,
@I_vtable_path_name = @POPHdrTbl,
@I_vrow_id = @DexRowID,
@O_oExists = @oExists output,
@O_oInsertStatus = @OInsStatus output,
@O_iErrorState = @DexLockErrorState output
select @iError = @@error
/* Handle locking errors */
if ((@OInsStatus <> 1) or (@DexLockErrorState <> 0) or (@iError <> 0))
begin
if (@DexLockErrorState <> 0)
begin
select @oErrString = rtrim(@oErrString) + ' ' +
@DexLockErrorState
end
select @O_iErrorState = 9184
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@O_oErrorState output
return (@O_iErrorState)
end
end
Add SQL statements to update an existing record
The stored procedure must contain code to update an existing record in the Dynamics GP database. Use the value of the @exists variable that you set earlier to determine whether to update a record. If @exists is true (1), update the existing record in the database. Use the input parameters and variables to supply data values for each data field of the specified record.
The following script sample shows how to check the value of the @exists variable and how to add a SQL statement that updates a lead.
/** If the specified record exists, update the existing record in IG001 **/
if(@exists <> 0)
begin
update IG001 set
LeadName = @I_vLeadName,
SLPRSNID = @I_vSLPRSNID,
CITY = @I_vCITY,
STATE = @I_vSTATE,
ZIP = @I_vZIP,
ADDRESS1 = @I_vADDRESS1,
ADDRESS2 = @I_vADDRESS2,
PHONE1 = @I_vPHONE1,
PHONE2 = @I_vPHONE2,
FAX = @I_vFAX,
LeadBusinessCategory = @I_vLeadBusinessCategory,
COUNTRY = @I_vCOUNTRY,
CONTACT = @I_vCONTACT,
PotentialRevenue = @I_vPotentialRevenue,
QualifiedLead = @I_vQualifiedLead,
LeadSource = @I_vLeadSource,
QualificationDate = @I_vQualificationDate
where LeadID = @I_vLeadID
if(@@error <> 0)
begin
select @O_iErrorState = 61071 /* An update error occurred */
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@iAddCodeErrState output
return (@O_iErrorState)
end
end
return (@O_iErrorState)
GO
Remove the lock (optional)
If you set a lock on the record, you must release the lock. The following sample shows how to use taDEXLOCKS to release a lock on a purchase order. Notice how the value of the @_vOperation parameter is now set to "3".
if (@lock = 1)
begin
exec @iStatus = DYNAMICS..taDEXLOCKS
@I_vOperation = 3,
@I_vtable_path_name = @POPHdrTbl,
@I_vrow_id = @DexRowID,
@O_oExists = @oExists output,
@O_oInsertStatus = @OInsStatus output,
@O_iErrorState = @DexLockErrorState output
select @iError = @@error
/* Handle locking errors */
if ((@iStatus <> 0) or (@DexLockErrorState <> 0) or (@iError <> 0))
begin
if (@DexLockErrorState <> 0)
begin
select @oErrString = rtrim(@oErrString) + ' ' +
@DexLockErrorState
end
select @O_iErrorState = 9222
exec @iStatus = taUpdateString
@O_iErrorState,
@oErrString,
@oErrString output,
@O_oErrorState output
end
end
Specify security permissions for the stored procedure
To allow access to the stored procedure, specify the security permissions required to run this stored procedure. Dynamics GP eConnect grants execute permissions to DYNGRP.
GRANT EXEC ON dbo.sampleLeadCreateUpdate TO DYNGRP GO
Save the file
From the File menu, choose Save.