topic Re: Stored Procedure in QlikView

Stored Procedure

Anonymous — Mon, 03 Nov 2014 15:39:49 GMT

Hi guys,

I am trying to execute a stored procedure with a variable. I've created the variable and loaded the script.

Load * ;

SQL EXECUTE sp_CustomerPriceList '$(vCustNo)';

After the load has completed successfully i cannot see any fields to choose when creating a table within my sheet based on the results of the stored procedure.

Has anyone seen anything like this before? Or can offer any advice?

Thanks

Re: Stored Procedure

maxgro — Mon, 03 Nov 2014 15:50:30 GMT

yes,

example I have this for test a stor proc in Qlik and SqlServer (and it works)

Qlik

load *;

sql execute sp_test '%MED%';

Sql Server

ALTER PROCEDURE [dbo].[sp_test]

@p_banca varchar(10)

BEGIN

SET NOCOUNT ON;

SELECT * from d001_banche where des_banca like @p_banca;

END

Re: Stored Procedure

maxgro — Mon, 03 Nov 2014 15:58:26 GMT

and this also works, with a variable

set var='%E%';

load *;

sql execute sp_test '$(var)';

Re: Stored Procedure

Anonymous — Mon, 03 Nov 2014 16:00:21 GMT

I wonder if it is because i am using a temp table within the stored procedure?

Should I try and remove that and create the table instead?

Re: Stored Procedure

Mon, 03 Nov 2014 16:02:20 GMT

Hi,

Are you facing any errors?

Please check if you have duplicate field names ....most of the time script fails due to field name duplication...

Check log file for more information....

Re: Stored Procedure

Mon, 03 Nov 2014 16:09:50 GMT

You could try naming the i,e when you call it use something like dbo.sp_test1

Re: Stored Procedure

Anonymous — Mon, 03 Nov 2014 16:35:53 GMT

I am getting there! I just needed to select the results of the table within the load script.

What i am trying to achieve i don't think is possible. I have a variable within my sheet which is called VCustNo and the user can enter a customer number in here. What i would like to happen is for the stored procedure to execute after a customer number is entered...

Does this make sense?

Re: Stored Procedure

Peter_Cammaert — Mon, 03 Nov 2014 16:46:04 GMT

Well, in QV Country this doesn't make that much sense. On average, you'll be loading all customer information straight into your QV document, upon which the user simply points to a CustomerNo and gets results immediately. Withtout rerunning the script every time new data is needed.

If the data is massive, you could try DIRECT DISCOVERY to get the details interactively, once your user has decided on a customer to focus on.

Peter

Re: Stored Procedure

Anonymous — Mon, 03 Nov 2014 16:48:34 GMT

Just thinking aloud !

Could I create a button to execute the stored procedure?

Re: Stored Procedure

maxgro — Mon, 03 Nov 2014 17:52:26 GMT

You can use an input box to get the customer from the user and a button to trigger a reload (with an action)

This works for Qlik desktop.

In server I think you have to use EDX or something else.

But I strongly agree with Peter, this doesn't make that much sense.

Re: Stored Procedure

Anonymous — Mon, 03 Nov 2014 18:11:42 GMT

It's because we have thousands of different customers with different price lists. When you execute the stored procedure against one customer you have 83,000 rows and this is just for that one customer!

The way i envisage this working is the user enters a customer number which is done via a Variable/Input box and then you can press a button which will execute the stored procedure against the customer which is entered into the variable. I've ran the stored procedure in management studio and it only takes around 10 seconds to complete so this shouldn't take too long at all.

If this isn't possible then i shall just park this idea!..

Thanks for your advice anyway so far though guys

Re: Stored Procedure

Anonymous — Tue, 04 Nov 2014 14:26:22 GMT

Can anyone offer any further help with this or is it a no goer?

Re: Stored Procedure

Tue, 04 Nov 2014 14:29:14 GMT

You could store the command in a batchfile and execute the batch through a macro button, i wouldnt really advise it but it is possible.

Re: Stored Procedure

Anonymous — Tue, 04 Nov 2014 15:50:10 GMT

Sounds interesting.

Any idea how? Haha

Re: Stored Procedure

Dalton_Ruer — Thu, 05 May 2016 20:39:38 GMT

I know it's been a long time since you asked but I wanted to post here in case people searched and found this.

I just posted a document on this topic When is a Result Set not a Result Set? and the answer is Yes Qlik would have problems if you create a temporary table inside your stored procedure, but you can use SET NOCOUNT ON in SQL Server to avoid the issue so that Qlik will be able to see the right results.