Execute stored procedure in Acumatica

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());

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