Loading ...

Execute Stored Procedure In Acumatica

11 August 2014

Hello everybody.

I want to share if you need to execute stored procedure in Acumatica how to achieve it. 

It's simple.

1. Create Stored procedure in db.

2. Use in your code PXDatabase.Execute.

For example let's say you created stored procedure which has name DeletePrTranByPrPayrollAndPayrollDetailsId and takes as parameters @prPayrollRefNbr and @prPayrollDetailsID which are of type nvarchar.

Then you can call stored procedure in the following way:

var pars = new List<PXSPParameter>();

PXSPParameter p1 = new PXSPInParameter("@prPayrollRefNbr", PXDbType.NChar, details.PayrollRefNbr);

PXSPParameter p2 = new PXSPInParameter("@prPayrollDetailsID", PXDbType.NChar, details.PRPayrollDetailID);

pars.Add(p1);

pars.Add(p2);

PXDatabase.Execute("DeletePrTranByPrPayrollAndPayrollDetailsId", pars.ToArray());

Ready to take your Acumatica customization to the next level? Whether you need to execute stored procedures like the example above or have a unique business requirement that demands tailored solutions, our team is here to help. Share your customization ideas with us, and let’s transform your Acumatica experience together. Leave a request today, and let’s build something extraordinary!

3 Comments

  • Chris H said 2 years ago

    I recommend to include the execute statement inside the PXTransactionScope

  • David Eichner said 18 months ago

    I tried using your example but get errors:
    The type or namespace name 'List' could not be found

    A local variable named 'pars' cannot be declared in this scope because it would give a different meaning to 'pars'

    A local variable named 'p1' cannot be declared in this scope because it would give a different meaning to 'p1'

  • docotor said 18 months ago

    David, you also will need to add following usings:

    using System.Collections;
    using System.Collections.Generic;
    using System.Linq;

    without them List will not be available for you