Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
akumar_c
Contributor

Store procedure with parameters

Hi,

I have a procedure with 5 parameters, Now I want to execute it in QlikView and want to Store all the records in QVD. I am using below the following script but it is giving error. So please 'let me know how it can be resolve.

Error is :


Script Which I am using in QlikView Script:

usp_EPCM_Get_MultipleSearch:

SQL EXECUTE [dbCertifiedInventory_Live].[dbo].[usp_EPCM_Get_MultipleSearch]

@LotID varchar(MAX)='262,260',

@FromDate varchar(150)='',               

@ToDate varchar(150)='',                            

@JewlexRapPriceDate datetime = '01-Apr-2014',

@GIARapPriceDate datetime = '01-Apr-2014';

Store usp_EPCM_Get_MultipleSearch into E:\Amit Kumar\EPCM\usp_EPCM_Get_MultipleSearch.Qvd;


Thanks

14 Replies

Re: Store procedure with parameters

Strange. A typical Stored Procedure call should look like this:

usp_EPCM_Get_MultipleSearch:

SQL EXEC [dbCertifiedInventory_Live].[dbo].[usp_EPCM_Get_MultipleSearch] '262.260', '', '', '01-Apr-2014', '01-Apr-2014';

STORE usp_EPCM_Get_MultipleSearch INTO ...


Now, the syntax for everything between SQL and the semicolon should follow T-SQL rules, as it will be sent to the DB Engine just as if it came from a native SQL Server query. Your RDBMS is complaining that the calling format isn't correct, but that's more of a topic for a SQL Server forum...

akumar_c
Contributor

Re: Re: Store procedure with parameters

Hi Peter,

Thanks for reply...

Please let me know why it is storing in my defined path.

Error is :

Re: Store procedure with parameters

The message says that the Reload engine cannot find the table to store. Most probably this is because the SQL EXEC returns 0 rows. In that case QlikView decides to not create a internal table called usp_EPCM_Get_MultipleSearch  and therefor the STORE statement will fail.

MVP
MVP

Re: Store procedure with parameters

Try removing comma from the number, like:

@LotID varchar(MAX)='262,260',

@LotID varchar(MAX)='262260',

akumar_c
Contributor

Re: Store procedure with parameters

Is there any way to store the same SP information in QVD.

akumar_c
Contributor

Re: Store procedure with parameters

Hi tresesco,

Not done.

Re: Store procedure with parameters

I don't understand what you mean with "SP information"? The only interface to an RDBMS is via SQL queries that are sent as-is to the DB engine. The SQL EXEC you are using is just another SQL statement but it differs from a regular SQL SELECT in that it doesn't instruct the RDBMS to go and look for data rows, it just instructs the RDBMS to execute a predefined routine (a piece of code) that is stored in the DB itself. You could replace an SQL EXEC by a (probably) very complex SQL query in your QV Load Script but that would let performance take a deep dive.

A QVD is not a DB engine; it's just a file that stores a single table and is generated from an existing internal QV table by way of a single STORE statement. All this happens locally on your development/server platform  A QV therefor cannot be used to store database code, unfortunately.

I think you're stuck with your Stored Procedure. But that's not a bad thing. You'll only need to figure out how to properly call the SP. Can you get in touch with the developer of the Stored Procedure?

Peter

akumar_c
Contributor

Re: Store procedure with parameters

Yes I contact the SQL Developver. When I am executing the same SP with the same parameter I am getting 85 no. of rows, Same data I want to store in QVD by using same SP with the same parameter.

Thanks.

MVP
MVP

Re: Store procedure with parameters

Hi

Remove the type definitions in the parameters, and conditionally store the results if data is returned:

usp_EPCM_Get_MultipleSearch:

SQL EXECUTE [dbCertifiedInventory_Live].[dbo].[usp_EPCM_Get_MultipleSearch]

  @LotID = '262,260',

  @FromDate = '',              

  @ToDate = '',                           

  @JewlexRapPriceDate = '01-Apr-2014',

  @GIARapPriceDate = '01-Apr-2014';

If Alt(NoOfRows('usp_EPCM_Get_MultipleSearch'), 0) > 0 Then

  Store usp_EPCM_Get_MultipleSearch into E:\Amit Kumar\EPCM\usp_EPCM_Get_MultipleSearch.Qvd;

End If

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser