Store Procedure in qlik sence

[QlikLearner_2022]

Hi Team

I have a requirement where i need to use single line store Proc and create all the parameters in qlik sence

while i'm creating variables and trying to run report i'm getting error

could someone please help me in this regard

my Script;

LIB CONNECT TO 'Microsoft_SQL_Server';

Let vcUser ='user;
let vcArea ='AreaName';
Let Vstart='2022-01-01';
Let Vend='2024-12-12';

SQL;
execute dbo.uspGetProductName;

could someone guide me where i'm doing wrong, i'm getting error near SQL

[AronC]

@QlikLearner_2022 

You have missed a ' in the first row

Let vcUser ='user;

Should be 

Let vcUser ='user';

[QlikLearner_2022]

thats my typo in the question i'm sorry for that i'm getting error near SQL

[AronC]

Have you checked this article?

https://community.qlik.com/t5/Qlik-Sense-Documents/Store-a-QlikSense-table-directly-into-an-SQL-tabl...

[vinieme12]

remove the ";"  after SQL

SQL indicates that the following code until a ";" is a sql block

SQL
execute dbo.uspGetProductName

;

Also can you post a screenshot of the error message??

[QlikLearner_2022]

My Actual Store Proc is

USE [DB]
GO

DECLARE @return_value int

EXEC @return_value = [DB].[usp_StorePROC]
@vcDOHUser = USER,
@dtStart = "2022-01-01",
@dtEnd = "2024-12-12",
@vcArea = NULL,
@vcCounty = NULL,
@vcUser = NULL,
@vcAgeGroupType = NULL,
@vcAgeGroup = NULL,
@vcDisease = NULL,
@vcProviderType = NULL,
@vcPublicIndicator = NULL

SELECT 'Return Value' = @return_value

GO

could you please tell me how can i write it in Qlik Sence

[vinieme12]

LIB CONNECT TO 'Microsoft_SQL_Server';

SQL

USE [DB]
GO

DECLARE @return_value int

EXEC @return_value = [DB].[usp_StorePROC]
@vcDOHUser = USER,
@dtStart = "2022-01-01",
@dtEnd = "2024-12-12",
@vcArea = NULL,
@vcCounty = NULL,
@vcUser = NULL,
@vcAgeGroupType = NULL,
@vcAgeGroup = NULL,
@vcDisease = NULL,
@vcProviderType = NULL,
@vcPublicIndicator = NULL

SELECT 'Return Value' = @return_value

GO

;

[QlikLearner_2022]

@AronC  thanks for sharing the link but my requirement is to send the variable selections back to Store Proc where all the calculations/filters are maintained

[QlikLearner_2022]

@vinieme12 

the script i'm using

LIB CONNECT TO 'Microsoft_SQL_Server

SQL

// USE [StarsNew]
// GO

DECLARE @return_value int;

execute StarsReport.DB
@vcDOHUser = 'USER'
,@dtStart = '8/11/2019'
,@dtEnd = '9/15/2019'
,@vcArea = 'AreadID'
,@vcCounty = 'CountyID'
,@vcUser = 'UserName'
,@vcAgeGroupType = 'Regular'
,@vcAgeGroup = 'AgeGroupId'
,@vcDisease = 'DiseaseId'
,@vcProviderType = 'ProviderTypeId',
@vcPublicIndicator = 'Yes';

SELECT 'Return Value' = @return_value

GO

;

I'm getting this error

[vinieme12]

add the !EXECUTE_NON_SELECT_QUERY clause at the end

try below

LIB CONNECT TO 'Microsoft_SQL_Server

SQL

// USE [StarsNew]
// GO

DECLARE @return_value int;

execute StarsReport.DB
@vcDOHUser = 'USER'
,@dtStart = '8/11/2019'
,@dtEnd = '9/15/2019'
,@vcArea = 'AreadID'
,@vcCounty = 'CountyID'
,@vcUser = 'UserName'
,@vcAgeGroupType = 'Regular'
,@vcAgeGroup = 'AgeGroupId'
,@vcDisease = 'DiseaseId'
,@vcProviderType = 'ProviderTypeId',
@vcPublicIndicator = 'Yes';

SELECT 'Return Value' = @return_value 

GO

!EXECUTE_NON_SELECT_QUERY;

also check below

Connector reply error: Executing non-SELECT querie... - Qlik Community - 1716322