X++ Stored Procedure Calls for Dynamics 365

In this article, we are going to look at how to create, drop, and execute stored procedures from X++ code. Using stored procedure calls in Dynamics 365 allows us to execute long running queries much faster than X++ logic alone.

I recently saw a user accidentally created hundreds of thousands of records in the DocuRef table with blank notes. We wanted to create a batch job that would allow a user to select a number of notes to delete and have a stored procedure clean up that many records with blank notes.

We were able to accomplish this with the following code. This batch job service class contains methods to drop, create, and execute stored procedures. Please use these as a reference and make the appropriate adjustments to meet your teams’ requirements.

/// <summary>
/// DocuRefCleanup
/// </summary>
public class DocuRefCleanupService
{
    str executeSPStatement()
    {
        str ret = @"EXECUTE DocuRefCleanup";
        return ret;
    }

    str dropSPStatement()
    {
        str ret = @"IF EXISTS(SELECT 1 FROM sys.procedures WHERE Name = 'DocuRefCleanup')
            BEGIN
                DROP PROCEDURE dbo.DocuRefCleanup
            END";
        return ret;
    }

    str createSPStatement(int _numberOfRecordsToDelete)
    {
        str ret = @"CREATE PROCEDURE [dbo].[DocuRefCleanup]
            AS
            BEGIN TRANSACTION
                DELETE TOP (" + int2Str(_numberOfRecordsToDelete) + @") FROM DOCUREF where Notes is NULL and TYPEID = 'Note'
            COMMIT TRANSACTION;";
        return ret;
    }

    void executeSQL(str _tSQL)
    {
        Connection connection = new Connection();
        Statement statement = connection.createStatement();

        SqlStatementExecutePermission permission;
        permission = new SqlStatementExecutePermission(_tSQL);
        permission.assert();
        try
        {
            statement.executeUpdate(_tSQL);
        }
        catch
        {
            throw Error("@Label:DocuRefCleanupError");
        }
        finally
        {
            CodeAccessPermission::revertAssert();

            if (statement != null)
            {
                statement.close();
            }
        }
    }

    /// <summary>
    /// Removes DocuRef records with blank notes
    /// </summary>
    public void cleanupDocuRef(DocuRefCleanupContract _contract)
    {
        if (!_contract.parmNumberOfRecordsToDelete())
        {
            throw error("Missing parameter.");
        }

        // Drop stored procedure if it exists
        str statement = this.dropSPStatement();
        this.executeSQL(statement);

        // A new stored procedure will be create in AXDB >> Programmability >> StoredProcedures
        statement = this.createSPStatement(_contract.parmNumberOfRecordsToDelete());
        this.executeSQL(statement);

        // Execute stored Procedure
        statement = this.executeSPStatement();
        this.executeSQL(statement);
    }
}

For more Dynamics 365 tech guidance or questions about X++ Stored Procedure Calls for Dynamics 365, connect with us here.

Post originally published https://markedcode.com/index.php/2024/01/24/x-stored-procedure-calls/