X++ Stored Procedure Calls

In this article, we are going to look at how to create, drop, and executing stored procedures from X++ code. Using stored procedures can allow 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);
    }

}

Keep reading about D365 tips and tricks here: https://markedcode.com/index.php/category/d365/