Forum Discussion

tpoirier's avatar
tpoirier
Contributor
12 years ago
Solved

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





9 Replies

Sort By
  • agabriel's avatar
    agabriel
    Contributor
    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();


     


    }

  • agabriel's avatar
    agabriel
    Contributor
    12 years ago
    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;




  • agabriel's avatar
    agabriel
    Contributor
    12 years ago
    As for passing a null value, try:




      SProc.Parameters.AddParameter();


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


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



    without quotations.

  • tpoirier's avatar
    tpoirier
    Contributor
    12 years ago
    Andrew, 



    That works perfectly, same result that I get if query the db, thanks for your help!
  • agabriel's avatar
    agabriel
    Contributor
    12 years ago
    Can you provide more information about how you are trying to accomplish this?
  • tpoirier's avatar
    tpoirier
    Contributor
    12 years ago

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

  • tpoirier's avatar
    tpoirier
    Contributor
    12 years ago
    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
  • agabriel's avatar
    agabriel
    Contributor
    12 years ago
    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;