mysql - initialize local variable (in stored procedure) from a query result

Question

I am trying to create a stored procedure that would perform the select shown as the line before the end. It would take the earliest StartDate and the latest EndDate from my usertable and select all days between from those dates from my reference calendar table. Unfortunately whatever syntax I will try to use I will always get an error message on assigning the values from usertable selects to start/endday variables. I have not found many resources specifying the correct syntax from examples I have seen I tried the following.

DELIMITER $$
CREATE PROCEDURE seldays() 

BEGIN 

DECLARE startday DATE;
SET startday = (SELECT MIN(StartDate) from user1table1);
DECLARE endday DATE;
SET endday = (SELECT MAX(EndDate) from user1table1);
SELECT calendar_date FROM calendar WHERE calendar_date >= startday AND calendar_date< endday;

END $$
DELIMITER ;

I also tried to assign values thusly

SELECT MIN(StartDate) from user1table1 INTO endday;

To no avail. I might be able to make the procedure work without storing these variables - but it is something that I will sooner or later need to use anyway. So how does one fill local variables with query results in Mysql correctly?

Why not simple SELECT calendar_date FROM calendar c, (SELECT MIN(StartDate) mindate, MAX(StartDate) maxdate from user1table1) u where c.calendar_date >= u.mindate and c.calendar_date <= u.maxdate) ? Why a procedure instead of a simple select? — Jorge Campos
– Jorge Campos, Commented Feb 7, 2017 at 11:49

Gordon Linoff · Accepted Answer · 2017-02-07 11:50:38Z

2

I would just do this in a single query. Why bother with variables?

SELECT c.calendar_date
FROM calendar c JOIN
     (SELECT MIN(StartDate) as startday, MAX(EndDate) as endday
      FROM user1table1
     ) u
     ON c.calendar_date >= u.startday AND c.calendar_date < u.endday;

answered Feb 7, 2017 at 11:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jens-Konrad Preem Over a year ago

Could work - but nevertheless I guess I might some day need a way to store some query results in local variables and it just doesn't seem to work right now.

Jens-Konrad Preem · Accepted Answer · 2017-02-07 12:38:41Z

2

Thanks to the comment "Declare variable should be on top" by Ankit Agrawal ..

I can conclude this thing answered. I post this post as a correct answer so If anyone has the same problem they can quickly look.

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

DELIMITER $$
CREATE PROCEDURE seldays() 

BEGIN 

DECLARE startday DATE;
DECLARE endday DATE;
SELECT MIN(StartDate) INTO startday FROM user1table1;
SELECT MAX(EndDate) INTO endday FROM user1table1;
SELECT calendar_date FROM calendar WHERE calendar_date >= startday AND calendar_date< endday;

END $$
DELIMITER ;

answered Feb 7, 2017 at 12:38

Jens-Konrad Preem

1873 silver badges19 bronze badges

1 Comment

Fakipo Over a year ago

can we initialize more than one variable from a single query in this way?

Shadow · Accepted Answer · 2017-02-07 11:49:31Z

0

In MySQL you can use the select ... into <variable list> from ... statement to assign results variables:

select min(StartDate) into startday from user1table1;
select max(EndDate) into endday from user1table1;

answered Feb 7, 2017 at 11:49

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

3 Comments

Jens-Konrad Preem Over a year ago

Hmm tried that still errors. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE endday DATE;

Shadow Over a year ago

Your error message is not related to assigning a value to the variables, your entire question is misleading.

Shadow Over a year ago

Your 2nd declare should be moved up to the front. All declares must come before any other statement.

halfer · Accepted Answer · 2017-08-22 13:23:48Z

0

SELECT MIN(StartDate) into startday from user1table1

edited Aug 22, 2017 at 13:23

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Feb 7, 2017 at 11:46

Sagar Gangwal

8,1303 gold badges28 silver badges45 bronze badges

Collectives™ on Stack Overflow

mysql - initialize local variable (in stored procedure) from a query result

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related