Stored Procedure Connection In QlikView Application

Introduction

This article describes Stored Procedures and the execution of Stored Procedures in a QlikView application.

What is Stored Procedure

A Stored Procedure is a collection of SQL statements that have been created and stored in a database. A Stored Procedure is compiled into a single execution. A Stored Procedure accesses a Relational Database System. Stored Procedure will accept only input parameters.

The following are advantages of using Stored Procedures:

• Generally Stored Procedures are non portable, that means they are specific to a specific RDBMS.
   
• Stored Procedure are pre-compiled, that means they can be reused without recompiling.
   
• Stored Procedures support a procedural programming language that consists of conditional statements and loops.
   
• Stored Procedures increase reliability.
   
• The database or DBA developer must have database rights that Stored Procedure require.
   
• Stored Procedures allow faster execution time.

Syntax of  executing Stored Procedure

The following is the basic syntax of a Stored Procedure.

EXEC proc_name  'paramValue1' 'paramValue2'

Stored Procedure in QlikView

QlikView applications support business environments and almost all business environments support complex business logic. So this business logic is implemented in a QlikView application.

A Stored Procedure is generally used in a QlikView application when the source system has a large transactional database system and a data warehouse is not supported.

Let's start with the connectivity of a Stored Procedure.

Step 1: Create Stored Procedure in SQL Server.

The first and major step is first we need to create a Stored Procedure in SQL Server.

ALTER PROCEDURE [dbo].[SelectPerson1]
 @City varchar(50), @Address varchar(50)
 AS
 BEGIN
 Select * from Person where [City]= @City
 Select * from Person where [Address]= @Address
 END
 
 exec SelectPerson1 'Kanpur', 'Dadar'

Step 2: Open the QlikView application

It is the first step, in this step you need to open the QlikView application then go to File-> New then this window will be opened.



Step 3: Open Edit Script

The second step is to open the edit script window from File-> Edit Script.


 

• Then this window will be opened.
  
  
   

Step 4: Select Database

The third step is to select the database from the data tab and click on the Connect Button.



Step 5: Data link property window

In this window we select SQL Server and click on the Next Button.



Step 6: Connection tab

In connection tab, you entered the server name and user name and password and select the database from your server and click on Test Connection.

Then you will see the test connection succeed and click on the OK button.


 

Then click on the OK button.


 

Step 7: SQL Server login

Then a SQL Server login is required so you must enter a valid login id and password and click on the OK button.

Then this window will be opened.


 

Step 7: In the edit script window we should manage these settings in the settings tab.



Step 8: Execute edit script

Now we execute the Stored Procedure in the QlikView edit script and reload it.



Step 9: Save QlikView file

After reloading the edit script, the next step is to save the QlikView file and click on the Save button.



Step 10: Script execution progress

Then a script execution progress window will be opened and a login Id and password is required, then click on the OKbutton.



Step 11: Sheet property Window

After this process, the sheet property window will be opened. Here we add those fields that we used as a parameter in the Stored Procedure and select a field from the available fields and click on the OK button.

Then this window will be opened.


 

Step 12: Main window

In the main window you can see the Stored Procedure execute both the parameter address and city and fetch the data.



Step 13: Select table box

Right-click anywhere in the blank sheet and select a table box for showing the full row of data, "New Sheet Object" ->  "Table Box...".



Step 14: New table box

In this window we select all fields for showing the full row of data and click on the OK button.



Then you can see in this window that it shows all the data of a single row.



Summary

This article introduced Stored Procedures and provided the basic concepts of them. It shows simple code that explains how Stored Procedures execute in SQL. It also describes how to execute a Stored Procedure in a QlikView application.