topic Stored Procedure in qlikview in QlikView

Stored Procedure in qlikview

Tue, 28 Oct 2014 19:48:53 GMT

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 !

Re: Stored Procedure in qlikview

jaimeaguilar — Tue, 28 Oct 2014 20:01:27 GMT

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

@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

Re: Stored Procedure in qlikview

Anonymous — Tue, 28 Oct 2014 20:05:53 GMT

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

Re: Stored Procedure in qlikview

Wed, 29 Oct 2014 07:07:44 GMT

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!

Re: Stored Procedure in qlikview

Wed, 29 Oct 2014 14:07:43 GMT

Any suggestions please?

@ maxgro

Re: Stored Procedure in qlikview

jaimeaguilar — Wed, 29 Oct 2014 15:10:56 GMT

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

Re: Stored Procedure in qlikview

narband2778 — Wed, 29 Oct 2014 15:19:44 GMT

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

Hope this helps.

Thanks,

Naresh