Friends,
As you all know, long running queries/business logic are resource hungry and slow your system down, resulting to the poor performance and potentially a bad user experience. Once you identify the slow running queries you will try different ways to improve the performance by tweaking the code.
Stored Procedures can be used as a modular programming which means create once, store and call for several times whenever it is required. This supports faster execution and it reduces network traffic too.
In this article, we will learn how to drop,create a stored procedure on the fly through X++ Code, and execute it. I have created a simple stored procedure as an example in the code.
Create [Or Copy] the below class as shown below. This class has some methods to drop, create and execute a stored procedure. Use/Tweak them as per your requirement.
class SRStoredProcedureHelper
{
public static void executeSQLQuery(str _statement)
{
Statement statement;
statement = new Connection().createStatement();
new SqlStatementExecutePermission(_statement).assert();
statement.executeUpdate(_statement);
}
public static void createSampleStoredProcedure(str _storeProcedureName, str _statement)
{
// This logic will create a new stored procedure by name “SRSampleSP” in AXDB Database
// Serialize access to manipulating of the stored procedure
using (var reqReaderWriterLock = ReqReaderWriterLock::construct())
{
reqReaderWriterLock.enterUpdateLock(_storeProcedureName);
SRStoredProcedureHelper::executeSQLQuery(_statement);
reqReaderWriterLock.exit(_storeProcedureName);
}
}
public static str dropSQLStatement()
{
str ret = @”declare @objectIdSP NVARCHAR(50)
SELECT @objectIdSP = name FROM sys.objects WHERE object_id =
OBJECT_ID(N'[SRSampleSP]’) AND type in (N’P’, N’PC’)
if @objectIdSP != ”
DROP PROCEDURE SRSampleSP”;
return ret;
}
public static str createSQLStatement()
{
// A very simple stored procedure to update a particular field in a table across all legal
entities. Create or modify the SQL strings based on your requirements.
str ret = @”CREATE PROCEDURE SRSampleSP AS
BEGIN
SET NOCOUNT ON;
–UPDATE custTable SET SRCreditLimit = 8000.00
END”;
return ret;
}
public server static void executeSP(str _storedprocedureName)
{
Connection connection = new Connection();
Statement statement = connection.createStatement();
str sql;
SqlStatementExecutePermission permission;
sql = strfmt(“EXEC [%1]”, _storedprocedureName);
permission = new SqlStatementExecutePermission(sql);
permission.assert();
try
{
statement.executeUpdate(sql);
}
catch
{
throw Error (“An error occured during the execution.”);
}
CodeAccessPermission::revertAssert();
}
}
Finally, create a new Runnable Class (Job) to call the stored procedure as shown below.
class SPCallStoredProcedure
{
public static void main(Args _args)
{
str statement;
// Drop stored procedure if it exists
statement = SRStoredProcedureHelper::dropSQLStatement(); // Change this method by passing SP name as per your need.
SRStoredProcedureHelper::executeSQLQuery(statement);
// Create stored procedure by name “SRSampleSP” in AXDB
// A new stored procedure will be create in AXDB >> Programmability >> StoredProcedures
statement = SRStoredProcedureHelper::createSQLStatement();
SRStoredProcedureHelper::createSampleStoredProcedure(“SRSampleSP”, statement);
// Execute stored Procedure
SRStoredProcedureHelper::executeSP(“SRSampleSP”);
}
}
Happy Dax6ng,
Sreenath Reddy
Sreenath Reddy G - D365 FO Technical Blog 