1,105,226 Community Members

Procedure

Member Avatar
VIPER5646
Junior Poster
114 posts since Mar 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi all I'm trying to write a Procedure to subtract days from a data but Ignoring weekends.
This is what I have so far but I have a Syntax error Unexpected END, Expecting ;
I have put a ; after the last END but still get the same error.

DELIMITER $$ 
CREATE PROCEDURE `production`.`new_procedure` (in fDate Date, fVal int) 
BEGIN 
Declare x int default 1; 
Declare NewDate Date; 
WHILE x <= fVal DO 
set NewDate=Date_SUB(fDate,interval x Day); 
SELECT 
CASE DAYNAME(NewDate) 
WHEN `Saturday` THEN fVal=fVal+1 
WHEN `Sunday` THEN fVal=fVal+1 
Else x=x+1 
END 
End 
END$$
Member Avatar
IIM
Master Poster
742 posts since Jun 2011
Reputation Points: 163 [?]
Q&As Helped to Solve: 169 [?]
Skill Endorsements: 24 [?]
 
0
 

i am not sure but it seems that your select statement is at wrong place.
Secondly after each Case condition,there must be semicolon.Check http://dev.mysql.com/doc/refman/5.1/en/case.html

Member Avatar
VIPER5646
Junior Poster
114 posts since Mar 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

Thanks IIm for your reply butI had to change the procedure to A function because the procedure does not return a value.
so now I have this fuction that there is no error messag in it.

delimiter $$
CREATE DEFINER=`root`@`localhost` FUNCTION `PrcssDate`( fDate Date, fVal int) RETURNS date
BEGIN
Declare x int default 1;
Declare NewDate Date;
WHILE x <= fVal DO
set NewDate=Date_SUB(fDate,interval x Day);
CASE 
WHEN DAYNAME(NewDate)=`Saturday`
THEN set fVal = fVal+1;
set x=x+1;
WHEN DAYNAME(NewDate)=`Sunday` 
THEN set fVal = fVal+1;
set x=x+1;
ELSE
set x=x+1;
return NewDate;
End Case;
End While;
END$$

My isue with it is that when I try to call the function using SELECT Production.prcssDate(2013-04-04,1) As P1; I get the error "SELECT Production.prcssDate(2013-04-04,1) As P1 LIMIT 0, 1000 Error Code: 1292 Incorrect date value: '2005' for column 'fDate' at row 1"

Member Avatar
VIPER5646
Junior Poster
114 posts since Mar 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

Thank you all for your help
I Have Solved it by adding apostrophes to the date and to 'Saturday' and 'Sunday'

Member Avatar
IIM
Master Poster
742 posts since Jun 2011
Reputation Points: 163 [?]
Q&As Helped to Solve: 169 [?]
Skill Endorsements: 24 [?]
 
0
 

VIPER5646-ifyour problem is solved then mark thread as solved.

Question Answered as of 11 Months Ago by IIM
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: