Please enable JavaScript to view this site.

Navigation: Using ASPRunnerPro > REST API Connections and Views

SQL Views

Scroll Prev Next More

 

You can now use any non-standard SQL Query to retrieve data i.e. you can use MySQL variables in your SQL Query or use stored procedures to retrieve data. Let's see how to work with stored procedures. In this example, we will work with MySQL and Northwind database.

 

First, let's create a stored procedure:

 

DELIMITER //  
CREATE PROCEDURE categories_search  
(IN a VARCHAR(50))  
BEGIN  
 SELECT * from categories  
 where Description like concat('%', a, '%');  
END //  
DELIMITER ;

 

It doesn't do much but returns a list of categories that contain the word passed there as a parameter. We can test in in phpMyAdmin this way:

 

call categories_search('fish')  

 

Once we made sure that stored procedure works we can create a SQL View based on this stored procedure call. We will use 'All fields search' variable as a parameter. Note that we added single quotes around the parameter since this is a text variable. And, of course, we didn't have to remember this variable name, we added it via Insert variable->All fields search.

 

scr_sql_view

 

Now we can run this procedure, get results back, add fields to the list and proceed to build the project.

 

Note: 'All fields search' parameter will be empty on the initial page load. You need to make sure that your stored procedure won't break if an empty parameter is passed there. This is, of course, not a problem, if your stored procedure doesn't take any parameters.