Stored Procedure Connection In QlikView Application

This article describes Stored Procedures and the execution of Stored Procedures in a QlikVirw 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.

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.

    window
     

Step 4: Select Database

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

select database

Step 5: Data link property window

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

data link

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.

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.

    SQL server

      Then this window will be opened.

      edit
       

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

    settings

    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.

    Script execution

    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.

      main sheet
       

    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.

    main window

    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...".

    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.

    New table

    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.