Skip to main content

Notifications

Supply Chain Management forum

Create Stored Procedure.

(0) ShareShare
ReportReport
Posted on by Microsoft Employee

Hi All,

I am new to Dynamics 365 F & O trying to create a stored procedure through X++ and blocked. I can create a procedure, but I am looking slightly more.

1. I want to check if there is a stored procedure if its not present create the SP with one parameter.

2. After creation, execute the SP by passing the parameter value.

3. After execution of SP. Drop the SP.

All this I can do it directly from SQL, however when I use the same query from X++ its failing with Syntax error. I am sure there are a lot of X++ Gurus in the forum and can help answer or provide some workaround.  

  • Blue Wang Profile Picture
    Blue Wang on at
    RE: Create Stored Procedure.

    Hi Mcdba_hnjet,

    You can search for the class AifChangeTrackingConfiguration in AOT.

        /// <summary>
        /// Creates stored procedures that are used in Change Tracking Configuration.
        /// </summary>
        /// <remarks>
        /// This method is internal. Do not customize this method.
        /// </remarks>
        server private static void createStoredProcedures()
        {
            ttsbegin;
            if (AifChangeTrackingConfiguration::checkStoredProcExists())
            {
                AifChangeTrackingConfiguration::createSqlCtTableConfigurationSP();
            }
            ttscommit;
        }
    

    But, You need to expand Class and write createSqlCtTableConfigurationSP() according to your logic.

     

    Besides, here's an example of calling stored procedures directly through X++.

            // Connect to SQL DB
            loginProperty = new LoginProperty();
            loginProperty.setServer('localhost');
            loginProperty.setDatabase('SQL_DB');
            odbcConnection = new OdbcConnection(loginProperty);
            statement = odbcConnection.createStatement();
    
            /*
              @in_customer_id                INT
              ,@modified                     SMALLDATETIME = null
            */
    
            sqlStatement = strfmt('EXEC procedureForTest ' +
                          ' %1,'        +   // CustomerId
                          ' %2'             // DateTime
                          , 20190408
                          , sqlSystem.sqlLiteral(DateTimeUtil::utcNow()));
    
            sqlStatementExecutePermission = new SqlStatementExecutePermission(sqlStatement);
            sqlStatementExecutePermission.assert();
            resultSet = statement.executeUpdate(sqlStatement);
            //resultSet = statement.executeQuery(sqlStatement);// I test this ,too
            CodeAccessPermission::revertAssert();
    
            if (resultSet.next())
            info(strfmt("Return: %1", resultSet.getInt(1)));
    


    Hope this helps!

  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Create Stored Procedure.

    Hello mcdba_hnjet,

    May I ask you to review your tags and correct them?

    I think you just put too many, which makes it confusing for other to search.

    As an example, where is the relationship to the Management Reporter, Retail, Financial Management?

    Would be great if you could clean that up.

    Many thanks,

    Ludwig

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: Create Stored Procedure.

    First of all, direct SQL is a bad idea and you can easily find lots of explanations (blogs and discussions) why in the web, so I assume that you know what and why you want to do.

    There is an example in the standard code, look at  AifChangeTrackingConfiguration class createStoredProcedures() method, there you can see how to check if it exists and create new one.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,836 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans