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.
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!
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
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.
André Arnaud de Cal...
291,965
Super User 2025 Season 1
Martin Dráb
230,836
Most Valuable Professional
nmaenpaa
101,156