Create Stored Procedure.

(0) Share

Report

Posted on by Community Member

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.

Categories:

Other

Retail

Supply Chain Management

Development / Customization / SDK

Documentation

Implementation Lifecycle

Financial Management

On-prem Implementation

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Mea_ 60,284 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

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

Was this reply helpful? Yes No
Blue Wang on at

Like (0)

Report
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!

Was this reply helpful? Yes No