https://www.sikich.com

X++ Stored Procedure Calls for Dynamics 365

Mark Wilson

Jun 20 2024

4 min read

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/

This publication contains general information only and Sikich is not, by means of this publication, rendering accounting, business, financial, investment, legal, tax, or any other professional advice or services. This publication is not a substitute for such professional advice or services, nor should you use it as a basis for any decision, action or omission that may affect you or your business. Before making any decision, taking any action or omitting an action that may affect you or your business, you should consult a qualified professional advisor. In addition, this publication may contain certain content generated by an artificial intelligence (AI) language model. You acknowledge that Sikich shall not be responsible for any loss sustained by you or any person who relies on this publication.

About the Author

Mark Wilson

Mark Wilson is a D365 Technical Solution Architect with a proven record of success in supporting clients through the software development lifecycle. He has a strong emphasis on technical integrations and leading technical workstreams. Mark focuses on a wholistic system architecture approach to allow for all systems used by a client to have optimal performance and business impact.