SQL Server stored procedures

Stimulsoft Dashboards.PHP discussion
Asawyer13
Posts: 16
Joined: Fri Aug 23, 2019 12:16 am

SQL Server stored procedures

Post by Asawyer13 »

I have a dashboard with 3 Stored Procedures on it.

The first stored procedure is going to get a value passed to it via the handler.php file.

The other 2 stored procedures use the store number and date values from two variables but when I run the report the report gives an error saying that the stored procedure expects a parameter.
Lech Kulikowski
Posts: 6197
Joined: Tue Mar 20, 2018 5:34 am

Re: SQL Server stored procedures

Post by Lech Kulikowski »

Hello,

Please send us a sample report with test data that reproduces the issue for analysis.

Thank you.
Asawyer13
Posts: 16
Joined: Fri Aug 23, 2019 12:16 am

Re: SQL Server stored procedures

Post by Asawyer13 »

I had not setup a default for the values in the stored procedure. I now default to Null, however even though the report works in Preview when I run it thru the php files I get no error, but also don't get any data.

Alan
Asawyer13
Posts: 16
Joined: Fri Aug 23, 2019 12:16 am

Re: SQL Server stored procedures

Post by Asawyer13 »

How can I send the test report? I can't send here as it contains proprietary data.
Asawyer13
Posts: 16
Joined: Fri Aug 23, 2019 12:16 am

Re: SQL Server stored procedures

Post by Asawyer13 »

I have sent test to support. It looks to me as though no parameters are being sent when the stored procedures are being run.

Any idea what I'm doing wrong>

Alan
Asawyer13
Posts: 16
Joined: Fri Aug 23, 2019 12:16 am

Re: SQL Server stored procedures

Post by Asawyer13 »

Ok, here is what I know. Been trying to get this to work for the last 6 hours. I think I know what the issue is, just not sure how to fix it.

In the Designer, it works when I have the following setup.

I have sp_Report1 as the stored procedure that I am running. It has two parameters. They are named Parm1 and Parm2, and again in designer they work. I can see in the sql server profiler that the two parameters are being added when the stored procedure runs.

In the report, I get no data and I can see that the parameters are NOT being added to the execution line of the stored procedure when it's run.

In designer, I don't have anything after the sp_Report1, in other words, no parameters, the designer is adding those for me automatically when it runs.

I can't find any pattern of stuff that I can try that works in both designer and once deployed,

Alan
Lech Kulikowski
Posts: 6197
Joined: Tue Mar 20, 2018 5:34 am

Re: SQL Server stored procedures

Post by Lech Kulikowski »

Hello,

Please clarify what version are you use? Parameters in the JS version were added in last build.
In old versions, you can use variables in the query, like:
exec procname {variable1}, {Variable2}

Thank you.
Asawyer13
Posts: 16
Joined: Fri Aug 23, 2019 12:16 am

Re: SQL Server stored procedures

Post by Asawyer13 »

I am using 2022.4.3 and php.

When I remove the parameters and use exec sp_Report1 {parm1}, {parm2} and try to Preview I get an exception.

Alan
Asawyer13
Posts: 16
Joined: Fri Aug 23, 2019 12:16 am

Re: SQL Server stored procedures

Post by Asawyer13 »

I had to put quotes around the bracket variables. Might be making progress.. I'll get back to you soon.
Asawyer13
Posts: 16
Joined: Fri Aug 23, 2019 12:16 am

Re: SQL Server stored procedures

Post by Asawyer13 »

With the quotes around the variables like '{parm1}' it works in the designer but not the php script. In the PHP script you doesn't replace the {parm1} with the value when it executes the stored procedure.

But if I leave the quote off, I get an exception in the designer.
Post Reply