topic Re: Execute stored procedure with parameters in Connectivity & Data Prep

Execute stored procedure with parameters

Anilb1603 — Mon, 23 Sep 2019 15:15:40 GMT

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

Re: Execute stored procedure with parameters

NadiaB — Mon, 23 Sep 2019 16:45:58 GMT

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/

Re: Execute stored procedure with parameters

Anilb1603 — Mon, 23 Sep 2019 18:01:54 GMT

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';

Re: Execute stored procedure with parameters

NadiaB — Mon, 23 Sep 2019 20:21:51 GMT

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:

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';

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!!

Re: Execute stored procedure with parameters

Anilb1603 — Tue, 24 Sep 2019 13:49:29 GMT

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

Re: Execute stored procedure with parameters

Anilb1603 — Tue, 01 Oct 2019 17:31:25 GMT

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

Re: Execute stored procedure with parameters

bisola1 — Sun, 09 Oct 2022 17:18:39 GMT

@Anilb1603 hope you are doing good, i am also going through the same issue as yours and am yet to get a solution to it, please can you let me know if you were able to find a solution to yours? and if yes , how did you go about it? your prompt response will be well appreciated. thank you so much.