How to use a stored procedure in custom.js?

Margarete_JussenMargarete_Jussen Customer IT Monkey ✭


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:

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

      DECLARE @return_value int

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

      SELECT 'Return Value' = @return_value

      alert (query);          
         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 ='/AssetManagement/Administration/PurchaseOrder/Edit/' + data[0].id, 'open_window','menubar, toolbar, location, directories, status, scrollbars, resizable,dependent');
         else if (data.length > 1){
         var newWindow ='', '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 />");
         else{ alert("Hier nicht gefunden")}
       error: function (){




Best Answer


  • Roland_KindRoland_Kind Partner Advanced IT Monkey ✭✭✭


    i have used the following approach:

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

    var newDataSource = new{
       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



    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.