In this article, we are going to look at how to create, drop, and executing stored procedures from X++ code. Using stored procedures can allow us to execute long running queries much faster than X++ logic alone.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
\/\/\/ <summary>\n\/\/\/ DocuRefCleanup\n\/\/\/ <\/summary>\npublic class DocuRefCleanupService\n{\n str executeSPStatement()\n {\n str ret = @\"EXECUTE DocuRefCleanup\";\n return ret;\n }\n\n str dropSPStatement()\n {\n str ret = @\"IF EXISTS(SELECT 1 FROM sys.procedures WHERE Name = 'DocuRefCleanup')\n BEGIN\n DROP PROCEDURE dbo.DocuRefCleanup\n END\";\n return ret;\n }\n\n str createSPStatement(int _numberOfRecordsToDelete)\n {\n str ret = @\"CREATE PROCEDURE [dbo].[DocuRefCleanup]\n AS\n BEGIN TRANSACTION\n DELETE TOP (\" + int2Str(_numberOfRecordsToDelete) + @\") FROM DOCUREF where Notes is NULL and TYPEID = 'Note'\n COMMIT TRANSACTION;\";\n return ret;\n }\n\n void executeSQL(str _tSQL)\n {\n Connection connection = new Connection();\n Statement statement = connection.createStatement();\n\n SqlStatementExecutePermission permission;\n permission = new SqlStatementExecutePermission(_tSQL);\n permission.assert();\n try\n {\n statement.executeUpdate(_tSQL);\n }\n catch\n {\n throw Error(\"@Label:DocuRefCleanupError\");\n }\n finally\n {\n CodeAccessPermission::revertAssert();\n\n if (statement != null)\n {\n statement.close();\n }\n }\n }\n\n \/\/\/ <summary>\n \/\/\/ Removes DocuRef records with blank notes\n \/\/\/ <\/summary>\n public void cleanupDocuRef(DocuRefCleanupContract _contract)\n {\n if (!_contract.parmNumberOfRecordsToDelete())\n {\n throw error(\"Missing parameter.\");\n }\n\n \/\/ Drop stored procedure if it exists\n str statement = this.dropSPStatement();\n this.executeSQL(statement);\n\n \/\/ A new stored procedure will be create in AXDB >> Programmability >> StoredProcedures\n statement = this.createSPStatement(_contract.parmNumberOfRecordsToDelete());\n this.executeSQL(statement);\n\n \/\/ Execute stored Procedure\n statement = this.executeSPStatement();\n this.executeSQL(statement);\n }\n\n}\n<\/pre>\n\n\n\n