How to use a stored procedure in custom.js?
Margarete_Jussen
Customer Adept 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+"   "+ "</a> <br />");
}
newWindow.document.close();
newWindow.focus();
}
else{ alert("Hier nicht gefunden")}
},
error: function (){
alert('Fehler');
},
});
}
});
Regards
Margret
Best Answer
-
Tom_Hendricks
Customer Super IT Monkey ✭✭✭✭✭
@Margarete_Jussen, it sounds like you won't see this right away, but hopefully it helps when you get back.
@Roland_Kind is showing a specific type of object using the Kendo UI controls that most of the forms and grids use. There are lots of good reasons for doing this, especially when binding to a grid as in his example, but it is certainly not necessary.
To make this more similar to your example, you would take some of the same properties out of his transport { read { ... } } properties and put them into your $.ajax() call. It will look similar when finished.
First, however, you need a stored dashboard query that calls your stored proc. You can either enter it through the portal (Data Sources in the Admin page) or you can run a query directly against the DB to create it. I found the latter to work better for me because I can script it for all my environments, but neither is necessarily better or worse--they get to the same result. Whichever method you choose to create this query, you need to know its ID. If you scripted it, you already know because you had to create the GUID for it. If you created it through the portal, you can look it up by its name, with a simple SQL Query:SELECT Id FROM ServiceManagement.dbo.DataSource WHERE Title = 'NAME OF YOUR QUERY'
This ID will be a 36-character GUID that you will use in your ajax call. I'll just refer to it as 'GUID' in the example, but you can also see where it is used in Roland's example above.EXEC @return_value = [dbo].[GetPurchaseorders] {{Objlabel}}</code>$.ajax({<br> url: "/api/v3/Dashboard/GetDashboardDataById",<br> data: {dateFilterType: "NoFilter", queryId: "GUID", Objlabel: objlabel},<br> type: "GET",<br> dataType: "json",<br> contentType: 'application/json; charset=UTF-8',<br> async: false,<br> success: function (data) { <br> // Do stuff with the data that is returned...<br> },<br> error: function () {<br> alert('Fehler');<br> }<br>});<br></pre>Note the "Objlabel" property right after the GUID. That is referring to the name of an input param that you need to place in your query somewhere. If you are executing a stored proc, then this would not be the name of its parameter necessarily, but rather the name that you are passing into it. For example, the query that you saved in the first step might look something like this:<br><br><pre class="CodeBlock"><code>
The word inside two braces {{ }}, which can be anything you want it to be, gets replaced by a javascript variable with nearly the same name "objlabel" following your example above. This could also have a different name if you wanted to make the difference more obvious than just the capitalization of the first letter. This convention is unique to dashboard queries, but extremely useful. To phrase this a different way, you have specified a placeholder called {{Objlabel}} in your query, and in your ajax call you set the value of this placeholder "Objlabel" equal to a javascript variable you defined earlier which is called "objlabel".
Again, I would follow Roland's example if your goal is to populate a Kendo control (especially a grid) that accepts a datasource object explicitly. It seems that you are opening a window with it, so his datasource object would not make sense in your case and I would do something like my example here, in that case. Different approaches for different purposes.
7
Answers
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 ...
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.
@Roland_Kind is showing a specific type of object using the Kendo UI controls that most of the forms and grids use. There are lots of good reasons for doing this, especially when binding to a grid as in his example, but it is certainly not necessary.
To make this more similar to your example, you would take some of the same properties out of his transport { read { ... } } properties and put them into your $.ajax() call. It will look similar when finished.
First, however, you need a stored dashboard query that calls your stored proc. You can either enter it through the portal (Data Sources in the Admin page) or you can run a query directly against the DB to create it. I found the latter to work better for me because I can script it for all my environments, but neither is necessarily better or worse--they get to the same result. Whichever method you choose to create this query, you need to know its ID. If you scripted it, you already know because you had to create the GUID for it. If you created it through the portal, you can look it up by its name, with a simple SQL Query:
This ID will be a 36-character GUID that you will use in your ajax call. I'll just refer to it as 'GUID' in the example, but you can also see where it is used in Roland's example above.
The word inside two braces {{ }}, which can be anything you want it to be, gets replaced by a javascript variable with nearly the same name "objlabel" following your example above. This could also have a different name if you wanted to make the difference more obvious than just the capitalization of the first letter. This convention is unique to dashboard queries, but extremely useful. To phrase this a different way, you have specified a placeholder called {{Objlabel}} in your query, and in your ajax call you set the value of this placeholder "Objlabel" equal to a javascript variable you defined earlier which is called "objlabel".
Again, I would follow Roland's example if your goal is to populate a Kendo control (especially a grid) that accepts a datasource object explicitly. It seems that you are opening a window with it, so his datasource object would not make sense in your case and I would do something like my example here, in that case. Different approaches for different purposes.