Adding a SQL stored procedure for a delete operation

Article
07/23/2010

To use the Dynamics GP Service framework to delete a document, you need to add a SQL stored procedure that removes the specified document from the Dynamics GP database. To create the SQL stored procedure, complete the following steps:

Open the Visual Studio solution

Use the same project you used for the Create/Update business object. Open the solution in Visual Studio.

Add a SQL script file

From the Project menu, choose Add SQL Script. In the Add New Item window, click SQL Script in the list of Templates. Enter a name for your script file, and then click Add.

Create a SQL stored procedure

Specify a name for the stored procedure and add the parameters. The following sample script creates a stored procedure named sampleLeadDelete. Notice that the only input parameter is the ID of the record to be deleted. The parameters also include the two required output parameters.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE type = 'P'
    AND name = 'sampleLeadDelete')
    BEGIN
        DROP  Procedure  dbo.sampleLeadDelete
    END

GO

CREATE Procedure dbo.sampleLeadDelete
(
    @I_vLeadID char(15),
    @O_iErrorState int OUTPUT,
    @oErrString varchar(255) OUTPUT
)

AS

set transaction isolation level read uncommitted
set nocount on

Declare and initialize local variables

Add variables you use in your stored procedure and supply a default value for each. The following script sample declares and initializes local variables. The sample also initializes the @O\_iErrorState, and @ErrString output parameter.

declare
    @iStatus int,
    @iAddCodeErrState int

select @O_iErrorState = 0,
    @iStatus = 0

if (@oErrString is NULL)
begin
    select @oErrString = ''
end

Validate the input parameters

Verify that the input parameter contains valid data. The following script sample shows how to validate the ID parameter.

if (@I_vLeadID is NULL)
begin
    select @O_iErrorState = 61050 /* The Lead ID value cannot be null */
    exec @iStatus =  taUpdateString
        @O_iErrorState,
        @oErrString,
        @oErrString output,
        @iAddCodeErrState output
    return (@O_iErrorState)
end

if (@I_vLeadID = '')
begin
    select @O_iErrorState = 61051 /* The Lead ID values is not valid */
    exec @iStatus = taUpdateString
        @O_iErrorState,
        @oErrString,
        @oErrString output,
        @iAddCodeErrState output
    return (@O_iErrorState)
end

Determine whether the record exists

Verify the input parameters contains a valid ID. The following script sample uses a query to identify whether the LeadID value is an existing lead. If the lead does not exist, an error is assigned to @O\_iErrorState, and the stored procedure ends.

select @I_vLeadID = UPPER(@I_vLeadID)

if (@I_vLeadID <> '')
begin
    if not exists (select 1 from IG001 (nolock) where LEADID = @I_vLeadID)
    begin
        select @O_iErrorState = 61052 /* The lead does not exist */
        exec @iStatus = taUpdateString
        @O_iErrorState,
        @oErrString,
        @oErrString output,
        @iAddCodeErrState output
    end
end

Delete the specified record

Use the SQL delete statement to remove the specified record. The following script sample shows how to use the SQL delete statement to remove a lead.

delete IG001 where LEADID = @I_vLeadID
if @@error <> 0
begin
    select @O_iErrorState = 61053
    exec @iStatus = taUpdateString
        @O_iErrorState,
        @oErrString,
        @oErrString output,
        @iAddCodeErrState output
    return (@O_iErrorState)
end

return (@O_iErrorState)
GO

Specify security permissions for the stored procedure

To allow access to the stored procedure, specify the security permissions required to run the stored procedure. Dynamics GP eConnect grants execute permissions to DYNGRP.

GRANT EXEC ON dbo.sampleLeadDelete TO DYNGRP
GO

Save the file

From the File menu, choose Save.

Share via