2. PHPRunner General questions
  3. Pass a date to stored procedure
This topic is locked

Pass a date to stored procedure

 		1/27/2023 2:41:43 AM
PHPRunner General questions
F
 fvdb author

I have a stored procedure that returns a stock, a parameter is a date

call showstock('2023-01-25');

How can i pass that date string to the stored procedure.
When the table has been generated I want to change the date and regeneration should occur.
admin 1/27/2023

You can achieve this if you create a SQL View in PHPRunner that is based on your stored procedure call:
https://xlinesoft.com/phprunner/docs/sql-views.htm
F
 		fvdb author 1/30/2023

Hi Admin,

The problem is that the filter is not returned in the stored procedure. Let me elaborate.

CREATE DEFINER=whs@% PROCEDURE sscccount(IN datum varchar(50))
BEGIN
set @a=datum;
select count(sscc) as amount ,b.groep,@a as peildatum, (select w.warehouse from SKUALL as S5 left join lokaties as l on S5.lokatieid=l.id left join warehouses as w on l.warehouseid=w.id where S5.sscc=b.sscc and date(S5.timestamp)<@a order by S5.timestamp limit 1) as warehouse from (select coalesce(sum(aantal),0) as aantal,sscc,a.groep from SKUALL left join artikelen as a on a.id=SKUALL.artikelid where sscc in (select distinct sscc from SKUALL as S1 where date(S1.timestamp)<@a) and SKUALL.id>=(select id from SKUALL as S2 where S2.sscc=SKUALL.sscc and (S2.typetelling=1 or S2.typetelling=2) order by S2.typetelling,S2.id desc limit 1) and DATE(SKUALL.timestamp)<@a group by SKUALL.sscc,a.groep) b where b.aantal>0 group by b.groep,warehouse order by warehouse,groep;
END

Now I need to pass the date @a to the stored procedure, currenty i am trying to create a database with just one date, and based upon that date create a qustom query, but it should be easier to do with the "SQL views" i guess.

The result must be that i get a stock on a particular date.

rgds
Fons
admin 1/30/2023

Your latest response didn't provide any new info. What exactly causes the trouble implementing this with the help of a SQL View?