Calling a stored procedure

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

 

}

  SProc.ExecProc(); 

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

  Log.Message(test);

Change:




and instead use something more like:

  SProc.Parameters.AddParameter();

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

  SProc.Parameters.Items(2).value = null;



without quotations.

Here is what I am working with, by creating all of the fields and passing in values I can get it to complete, but now I am working on trying to get the returned data back out. Additionally is there any way to pass in a value of null? In the Store List param the proc is set up to accept null and use the org id then but when I try to pass a null it does nothing. 



Thanks,



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 = "UserId";

  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.Parameters.CreateParameter("OnHand", adInteger, adParamReturnValue, 6, null);

  

  

  

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

  Log.Message(test);

  SProc.ExecProc(); 

SProc.Parameters.CreateParameter("OnHand", adInteger, adParamReturnValue, 6, null);

SProc.Parameters.AddParameter(); 

SProc.Parameters.Items(6).name = "OnHand";

SProc.Parameters.Items(6).DataType = adInteger; 

SProc.Parameters.Items(6).Direction = pdParamReturnValue; 

SProc.Parameters.Items(6).Value = null;