Store procedure with parameters

[sona_sa]

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

[Peter_Cammaert]

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...

[sona_sa]

Hi Peter,

Thanks for reply...

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

Error is :

[Peter_Cammaert]

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.

[tresesco]

Try removing comma from the number, like:

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

@LotID varchar(MAX)='262260',

[sona_sa]

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

[sona_sa]

Hi tresesco,

Not done.

[Peter_Cammaert]

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

[sona_sa]

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.

[jonathandienst]

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