Forum Discussion

tpoirier's avatar
tpoirier
Contributor
12 years ago

Calling a stored procedure

I am trying to call a stored procedure passing in several aruments and not having any success, with a trace I was able to find out that the parameter names were not being included when when the data i...
  • agabriel's avatar
    agabriel
    12 years ago
    For a dataset this might work:



    Instead of:




    var test = SProc.Parameters.ParamByName("OnHand").value;

    Log.Message(test);



    Try this:




    SProc.Open();


      SProc.First();


      while (! SProc.EOF)


      {


        Log.Message(SProc.FieldByName("OnHand").Value);


        SProc.Next();


      }


      SProc.Close();





tpoirier's avatar
tpoirier
Contributor
12 years ago



Working code for example going forward, this below connects to a server called 'DEV02' using Windows Authentication then given a stored proc name defines the table parameters. This is done by filling them in order so that is why the DEBUG field is used here. If I can figure out how to pass the param name in too I will update. Then it will rotate through the data results and log the data for the given column. 



 


function StoredProc () {


 


  var SProc = ADO.CreateADOStoredProc();


  SProc.ConnectionString = "Data Source=DEV02; Provider=SQLOLEDB;    Trusted_Connection=Yes; Integrated Security=SSPI; database=Rics";


  SProc.ProcedureName = 'dbo.Inventory_API_OnHandSummary';


  SProc.CommandTimeout = 3;


  


  


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(0).name = "OrganizationId";


  SProc.Parameters.Items(0).value = 'DB9AEE36-D771-405C-8D6E-9D9800EB73D0';


  


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(1).name = "RicsUserId";


  SProc.Parameters.Items(1).value = '71BA3107-A5B7-42F7-89CF-A044014A0CE4';


  


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(2).name = "StoreList";


  SProc.Parameters.Items(2).value = '1,5';


 


 


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(3).name  = "ItemList";


  SProc.Parameters.Items(3).value = "sku=CM574NE"; //need to form?


  


 


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(4).name = "IncludeNeagativeOnHand";


  SProc.Parameters.Items(4).value = 0;


  


  SProc.Parameters.AddParameter();


  SProc.Parameters.Items(5).name = "Debug";


  SProc.Parameters.Items(5).value = 0;


 


  


  SProc.Open();


  SProc.First();


  while(!SProc.EOF)


  {


    Log.Message(SProc.FieldByName("StoreCode").Value);


    SProc.Next();


  }


 


 


  SProc.Close();


 


}