Stored Procedure Connection In QlikView Application

Prerana Tiwari

QlikViewExecution of stored procedure in QlikViewExecution of stored procedure in SqlIntroduction of stored procedure

6 Comments

Join the conversation! Your thoughts help the community grow.

Edgardo Cervantes8y ago
Awesome and very detailed tutorial. Thanks a lot Prerana!
Yogesh Rade9y ago
Thanks for sharing this useful information.....
Arindam Roy12y ago
Hi Prerana Nice post thanks. 2 things 1. As the stored proc is parametrized can we have the values feed in runtime. Well answer is Yes, but when I deploy my solution it is not working. In desktop I need to reload to get the new values feed into stored proc, is there any way out of this. 2. The possible way out is EDX, do you have any other apporach to handle. Even if it EDX or any other can you please help to have step by step approach. Thanks Arindam
Arvind Pradhan12y ago
good......
Ravi Kumar12y ago
Really Good job nimit..
- Prerana Tiwari12y ago
  nimit???? nhi i m prerana ok

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.

After clicking New option

Step 3: Open Edit Script

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

window of 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.

Confirm box

Then click on the OK button.

ok box

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.

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.

reload

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.

save file

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.

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.

final window

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.