Qlik Community

Ask a Question

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Execute stored procedure with parameters

Hi Guys,

I have a stored procedure in Sql Server with 3 input parameters. I want to get all the data into qlik sense at once instead of going in a loop model providing input parameters every time. Is there a way to do this?

Do we have something like * we can supply as a parameter to get all the data at once? If not, Is there any alternate solutions available??

Any help would be appreciated.

Thanks,

Anil

5 Replies
Support
Support

Hi @Anilb1603 

We have an article that can help, it was written for QLikView but the syntax works for Qlik Sense too:

Calling a Stored Procedure in Microsoft SQL Server from QlikView

We encourage our customers to visit our Knowledge Base https://qliksupport.force.com/QS_CoveoSearch when trying to find a solution related and not limited to: errors, feature requests, product defects. You can also visit our Qlik Help portal https://help.qlik.com/

Note: if this suggestion helps to resolve the issue or answers your question, it will be appreciated if you select the "Accept as Solution" option.
We encourage you to visit our Knowledge Base https://qliksupport.force.com/QS_CoveoSearch when trying to find a solution related and not limited to: errors, feature requests, product defects. You can also visit our Qlik Help portal https://help.qlik.com/
Contributor III
Contributor III

Thank you @NadiaB for quick reply.

The instructions are not clear in that link. Can you please explain with some example if possible??

Below script is there in that link.

Executing a stored procedure with input / output parameters
SQL
declare @var3 int
execute sp_myProcedure 5,3, @var3 output
Select @var3 as result;

 

Executing a stored procedure returning a result set
SQL Execute CustOrderHist 'CustomerNo';
Support
Support

Hi @Anilb1603 

So basically you can create a store procedure that retrieves a data set (select (*) from table) or a stored procedure that returns a values using variables (OUTPUT VARIABLE)

NOTE: For this examples Microsoft AdventureWork2017 demo DB was used:

1)

Stored Procedure:

USE [AdventureWorks2017]
GO
CREATE PROCEDURE [dbo].[uspGetProductsByColor]
@Color varchar(15)
AS
BEGIN
SELECT *
FROM [Production].[Product]
WHERE Color= @Color;
END;

 

Call from Qlik Sense:

LIB CONNECT TO 'ODBC_AdventureWorks';

SQL Execute uspGetProductsByColor 'Silver';

 

2)

USE [AdventureWorks2017]
GO
CREATE PROCEDURE [dbo].[uspGetProductName]
@ProductID [int],
@Name varchar(50) OUTPUT
AS
BEGIN
SELECT Name
from [Production].[Product] p
WHERE p.ProductID = @ProductID
END;

Call from Sense (you can send it to a table or to a variable):

LIB CONNECT TO 'ODBC_AdventureWorks';

ProductName:
SQL declare @Name varchar(50)
execute uspGetProductName 476, @Name output
select @Name;

 

Hope it helps!!

Note: if this suggestion helps to resolve the issue or answers your question, it will be appreciated if you select the "Accept as Solution" option.
We encourage you to visit our Knowledge Base https://qliksupport.force.com/QS_CoveoSearch when trying to find a solution related and not limited to: errors, feature requests, product defects. You can also visit our Qlik Help portal https://help.qlik.com/
Contributor III
Contributor III

Thanks @NadiaB for helping me out.

Your example is clear now but In my case I have input parameters only. I do not have any output parameters.

And In your example, when you supplied 476 as a input parameter for Product Id how it would retrieve all the product Name values from underneath table. I believe it will retrieve product name for Id 476 only.

ProductName:
SQL declare @Name varchar(50)
execute uspGetProductName 476, @Name output
select @Name;

My requirement is to get all the product names into qlik sense at once. I am looking for a solution something like where I can supply Product Id = '*' and get all the product names into qlik sense at once.

Please let me know if any solutions available for my requirement.

Thanks,

Anil

Contributor III
Contributor III

Hi @NadiaB,

I am trying read the data from stored procedure, it has 3 input parameters. I wrote the sql script as below in qlik sense. The qlik sense load is went successful but I don't see any actual data loaded into qlik sense.

Am I missing anything here? Any help would be appreciated.

I used OLEDB connection here.

Data:
Load *;
SQL Exec InfDB_QA.dbo.spReport_Results
@TemplateId = 232
,@StartDate = '8/11/2019'
,@FinishDate = '9/15/2019'
;

Thanks,

Anil