CODEWITHSUNDEEP
mysqlsql
Nll
Nll

Reputation: 872

Syntax error using declare

I have syntax error when I use "declare" :

 delimiter $$
  begin

 declare @StartDay INT, @EndDay INT, @StartMonth INT, @EndMonth INT
 Select @StartDay = 01, @StartMonth = 10, @EndDay = 30, @EndMonth = 09

 Select year(a.created_at) as years , jd.JobDomain, count(a.Id) as nb_answer,   a.Job_id,   j.JobTitle 
 from JobAppliance a
 inner join Job j on a.Job_id =  j.PublicId
 inner join JobDestination d on j.Id=d.Job_id
 inner join Jjobdomain jd on  j.Id = jd.Job_id

 and 
 (
 @StartMonth = @EndMonth And 
 Month(a.created_at) = @StartMonth And 
 Day(a.created_at) >= @StartDay And 
 Day(a.created_at) <= @EndDay
 ) Or (
 @StartMonth != @EndMonth And (
 (
 Month(a.created_at) = @StartMonth And
 Day(a.created_at) >= @StartDay
 ) Or (
 Month(a.created_at) Between @StartMonth + 1 And @EndMonth - 1
 ) Or (
 Month(a.created_at) = @EndMonth And
 Day(a.created_at) <= @EndDay
 )
 )
)

group by year(a.created_at) , a.Job_id, j.JobTitle;

end$$
delimiter ;

Im using Mysql workbench

edit : 

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 
'@StartDay int, @EndDay int, @StartMonth int, 
     @EndMonth intSelect @StartDay = 0' at line 3

Upvotes: 1

Views: 354

Answers (1)

Sir Rufo
Sir Rufo

Reputation: 19116

You missed the Line Delimiter

DELIMITER $$

DECLARE MyVal INT$$

SELECT
...
$$

DELIMITER ;

On the Other hand, why do you have to change the Delimiter?

And Declare should only be used between BEGIN and END as stated in the Documentation. So if you are not using this part in a stored procedure (e.g. a simple Query) you can't use DECLARE

All Variables starting with an @ are Session Variables and do not need to be declared.

So here is a copy'n'paste for you

SET @StartDay = 01, @StartMonth = 10, @EndDay = 30, @EndMonth = 09; -- look here the ;

Select year(a.created_at) as years , jd.JobDomain, count(a.Id) as nb_answer,   a.Job_id,   j.JobTitle 
from JobAppliance a
inner join Job j on a.Job_id =  j.PublicId
inner join JobDestination d on j.Id=d.Job_id
inner join Jjobdomain jd on  j.Id = jd.Job_id

and 
(
@StartMonth = @EndMonth And 
Month(a.created_at) = @StartMonth And 
Day(a.created_at) >= @StartDay And 
Day(a.created_at) <= @EndDay
) Or (
@StartMonth != @EndMonth And (
(
Month(a.created_at) = @StartMonth And
Day(a.created_at) >= @StartDay
) Or (
Month(a.created_at) Between @StartMonth + 1 And @EndMonth - 1
) Or (
Month(a.created_at) = @EndMonth And
Day(a.created_at) <= @EndDay
)
)
)

group by year(a.created_at) , a.Job_id, j.JobTitle;

Upvotes: 1

Related Questions