Execute a SQL Stored Procedure

Submitted by Nathan Clouse on 08/08/11.

Last modified: 

02/04/17

Tags: 

Class(es): 

To execute a stored procedure stored in the AX database you are currently connected to, you can do the following:

 

server static void testSQLExecSproc(Args _args)
{
    UserConnection userConnection;
    Statement statement;
    str sqlStatement;
    ResultSet resultSet;
    SqlSystem sqlSystem;
    SqlStatementExecutePermission sqlPermission;
    ;

    sqlSystem = new SqlSystem();

    sqlStatement = 'EXEC dbo.Your_Stored_Proc %1, %2';

    sqlStatement = strfmt( sqlStatement, sqlSystem.sqlLiteral("Arg1"), sqlSystem.sqlLiteral("Arg2"));

    //Uncomment the following line to see what you are actually executing as SQL
    //info( sqlStatement );
    userConnection = new UserConnection();
    statement = userConnection.createStatement();

    sqlPermission = new SqlStatementExecutePermission(sqlStatement);
    sqlPermission.assert();

    resultSet = statement.executeQuery(sqlStatement);
    CodeAccessPermission::revertAssert();
}