Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You have missed a ' in the first row
Let vcUser ='user;
Should be
Let vcUser ='user';
thats my typo in the question i'm sorry for that i'm getting error near SQL
Have you checked this article?
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??
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
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
;
@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
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
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