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 is sent. Is there any way to force those to send? 



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





9 Replies

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








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


     


    }

  • It looks like the execution is happening at the end of the script. Try changing the last 3 lines to:




      SProc.ExecProc(); 


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


      Log.Message(test);




    Change:


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



    and instead use something more like:




    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;




  • As for passing a null value, try:




      SProc.Parameters.AddParameter();


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


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



    without quotations.

  • Andrew, 



    That works perfectly, same result that I get if query the db, thanks for your help!
  • Can you provide more information about how you are trying to accomplish this?

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

  • Thanks for the suggestions, I tried the null without quotes and got an error. Leaving it as an empty string will cause the proc to use the org value which will work for the moment. However getting the OnHand value is the issue now, it is not an output I realized rather it returns a dataset. How do I go about getting that back to a var to work with?



    Thanks
  • Ooopsss....I was editing my original reply for too long....



    Change:


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



    and instead use something more like:




    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;