How to use a stored procedure in custom.js?

Margarete_JussenMargarete_Jussen Customer IT Monkey ✭

Hello,

I want to use a stored procedure I have written (select of purchase orders with filter) that works in SQL Management Studio. I do not know how to use it in my custom.js. I tried already the same with a stored function and it worked but I Need a solution which gives me more than one value. Here is the Code with the function which works:

   $("#bestellAsset").keyup(function(event){
     if(event.keyCode == 13){ 
      var objlabel = document.getElementById('bestellAsset').value;
      var query ="select [UKA_Views].[dbo].get_purchaseOrder('"+objlabel+"') as id";
      
      USE [UKA_Views]
      GO

      DECLARE @return_value int

      EXEC @return_value = [dbo].[GetPurchaseorders]
      @Objlabel = N'135'

      SELECT 'Return Value' = @return_value

      GO
      
      alert (query);          
        $.ajax({
         url: "/Dashboard/GetDashboardQueryData?query=" + query,
         type: "GET",
         dataType: "json",
         contentType: 'application/json; charset=UTF-8',
      async: false,
      success: function (data) { 
         if (data.length == 1){
         alert ('1 gefunden')
         alert (data[0].id)
         var newWindow = window.open('/AssetManagement/Administration/PurchaseOrder/Edit/' + data[0].id, 'open_window','menubar, toolbar, location, directories, status, scrollbars, resizable,dependent');
         newWindow.focus();
         }
         else if (data.length > 1){
         var newWindow = window.open('', 'open_window','menubar, toolbar, location, directories, status, scrollbars, resizable,dependent');
         for(var d in data){
          newWindow.document.write("<a href=/AssetManagement/Administration/PurchaseOrder/Edit/" + data[d].Id+"&emsp;&emsp;&emsp;"+ "</a> <br />");
         }
         newWindow.document.close();
         newWindow.focus();
         }
         else{ alert("Hier nicht gefunden")}
       },
       error: function (){
          alert('Fehler');
       },
       
            });   

     }
   });

Regards

Margret

Best Answer

Answers

  • Roland_KindRoland_Kind Partner Advanced IT Monkey ✭✭✭

    Hi,

    i have used the following approach:

    I have created a grid - with the datasource ist defined as

    var newDataSource = new kendo.data.DataSource({
       transport: {
        read: {   
         url: "/api/v3/Dashboard/GetDashboardDataById",
         data: {dateFilterType: "NoFilter", queryId: "C41117BE-3FE5-93EA-0FB4-24B0058225CC", SearchText: mySearchText},
         type: "GET",
         dataType: "json",
        },
       },         
       group: {field: "WorkItemType" },
       pageSize: 10,
       serverPaging: false
      });

    so the result is displayed in the grid table


    hope this helps

    regards


    p.s.

    the query itself calls the stored table function ...

  • Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    This just solved a massive performance issue with one of the API calls that I thought I had to use.  Now I can query only for what I want.

    This is brilliant, @Roland_Kind!

    For those who are not aware from looking at it or from working with SQL table dashboard widgets, "SearchText" in the example can be called anything that you want, and you must place {{SearchText}} (use the same name) in your query in order to pick up the value that you are passing to it.

    I have not yet tried passing multiple variables, but if you are running SQL 2016 you could always pass in JSON and parse it.
  • Margarete_JussenMargarete_Jussen Customer IT Monkey ✭
    Hello, thanks you very much for your answers. I still did not manage to get your solution working. I am not so familiar with Java script data sources. I am sorry about it, I will try further after my vacation. Best regards Margret



  • Margarete_JussenMargarete_Jussen Customer IT Monkey ✭
    Thank you very much. It works now. :) 
Sign In or Register to comment.