Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Stored Procedure in qlikview

Hi,

I'm not able to execute the below stored procedure in qlikview. I followed some of the threads available in the community.

However I'm able to fix the OLEDB connection issue with mode is write in the connection string.

But not able to succeed with the sp below. However I used set NOCOUNT ON command in the starting of the statement, but it's throwing error.

Can anyone  please help on this?

SQL DECLARE @lsinBndrLID smallint,
@lintBlendCondID int,
@ldtQueryStart datetime,
@ldtQueryEnd datetime,
@lcurPRC CURSOR,
---------------

---------------

---------------
@ldatBlndingStarted datetime;

SQL DECLARE @tblBlndgResults TABLE
(
vchSKUName varchar(50),
vchBatchCode varchar(50),
vchPrdCode varchar(50),
datPRCCStarted datetime,
datBlndingStarted datetime
);


LET @ldtQueryStart = '20141005';
LET @ldtQueryEnd = '20141010';


LET @lsinBlndrLineID = 19;  
LET @lintBlndCondID = 2402; 

SET @lcurPRC = CURSOR FAST_FORWARD FOR;


SQL SELECT intStart, intEnd
FROM tblDatSgnl
WHERE intCondID = '$(@lintBlndCondID)'
AND intStart>DATEDIFF(s,'20000101','$(@ldatQueryStart)')
AND intEnd>DATEDIFF(s,'20000101','$(@ldatQueryStart)')
ORDER BY intStart;


OPEN @lcurPRC

FETCH NEXT FROM '$(@lcurPRC)'
INTO '$(@lintBlendStart)', '$(@lintBlendEnd)'

WHILE '$(@@FETCH_STATUS)' = 0
BEGIN

temp_Blndg_Started:
  EXEC  TOP 1
'$(@lvchSKUName)'=MIN(vchSKUName),
'$(@lvchBatchCode)'=MIN(vchBatchCode),
'$(@lvchProdCode)'=MIN(vchProdCode),
'$(@ldatPRCStarted)'=DATEADD(s, MIN(intStart), '20000101')
FROM tblDataLineTimes DLT
LEFT JOIN -------------
WHERE sinLineID = '$(@lsinBlndrLineID)'
GROUP BY intPRCID
HAVING MAX(intEnd) > '$(@lintBlndStart)'
AND MIN(intStart) < '$(@lintBlndEnd)'
ORDER BY MIN(intStart) DESC


INSERT INTO @tblBlndgResults VALUES
('$(@lvchSKUName,@lvchBatchCode,@lvchPrdCode,@ldatPRCCStarted,
DATEADD(s, @lintBlndStart, '20000101'))

FETCH NEXT FROM @lcurPRC
INTO @lintBlendStart, @lintBlendEnd

END

CLOSE @lcurPRC
DEALLOCATE @lcurPRC


Blndg_Started:
Load*;
SQL EXEC
vchSKUName [SKU Name],
datPRCStarted [PRC Started],
vchBatchCode [Batch Code],
vchProdCode [Product Code],
datBlendingStarted [Blending Started]
FROM @tblBlndgResults

Thanks in advance !

6 Replies
jaimeaguilar
Partner - Specialist II
Partner - Specialist II

Hi,

have you tried calling directly the SP inside QlikView, instead of pasting the code of the SP in QlikView?

What I mean is to extract data from SP like this:

QVTable:

SQL Execute SP_NAME 'Parameter1', 'Parameter2';

in your case it seems like your parameters are:

@lsinBndrLID

@lintBlendCondID

@ldtQuery

@ldtQuery

@lcurPRC


you can store these parameters in qlikview variables or send them directly. Remember that parameters need to be separated by commas and between single quotes (if you use variables inside QV you need to call them like '$(VariableName)' )


Just take care of the format needed for your parameters and the order of them,


Additionally, you may want to store the SP in a qvd with store statement,


regards

Anonymous
Not applicable
Author

Hi Tripathy,

Do this create Stored procedure with a name in SQL server for example your Stored procedure name is SPSample then you can call or excute as below statement.

tablename:

SQL

EXECUTE DATABASENAME.Schemaname.storeprocedurename;

store tablename into [$(path)tablename.qvd];

hope this helps

Not applicable
Author

Hi Jaime,

Thanks for the quick reply.

There is no Sp name defined in the script. Sorry but i'm not so familiar with Stored procedure.

As far as my understanding, my query throws error after the first sql statement and prior to the CURSOR statement.

I tried with the suggestions given by you. I get error as there is no SP defined.

Thanks in advance!

Not applicable
Author

Any suggestions please?

@

jaimeaguilar
Partner - Specialist II
Partner - Specialist II

You just have to create a Stored Procedure in SQL (with Create Procedure statement). once you have this, you can call the Stored procedure from QlikView with the Execute statement. There are plenty of documentation about how to create a SP in any Database,

regards

narband2778
Creator II
Creator II

Hi Tripathy,


Create s stored procedure in the database.


Connect to the database from Qlikview &

Run the stored Procedure Query in Qlikview.


Ex:

SQL

EXECUTE Database_Name.dbo.[SP_StoredProcedure1]

and put the below settings in the edit script

Stored Procedure.png

Hope this helps.

Thanks,

Naresh