CODEWITHSUNDEEP
mysqlstored-procedures
Portlight
Portlight

Reputation: 95

adding value to IN variable

I need to assign 21600000 to fromDate IF the value originally sent in is zero

Code that I have now is

CREATE PROCEDURE reportFreeCooling(
  IN fromDate VARCHAR (50),
  IN toDate   VARCHAR (50),
  IN timeZone VARCHAR (50)
)
BEGIN
  DECLARE startDate VARCHAR (50);
  DECLARE endDate   VARCHAR (50);
  DECLARE startDateOriginal VARCHAR (50);
  DECLARE mylogID   INT;
  DECLARE myItemId varchar (50);
  DECLARE myItemId2 varchar (50);
  DECLARE xHours varchar (50);
  DECLARE endHoursNull varchar(50);
  DECLARE endHoursNotNull varchar (50);

and of course the rest of the stored procedure. The rest is correct I just need to know how to change the fromDate if zero is what is sent in.

Upvotes: 1

Views: 57

Answers (2)

Portlight
Portlight

Reputation: 95

This piece of code actually required a ; at the end of the set statement and the end of the if statment. 

IF firstRowDate > startDateOriginal THEN SET startDateOriginal = firstRowDate; END IF;

Upvotes: 0

user2942090
user2942090

Reputation: 46

I haven't tested any of these but they may point you in the right direction.

OPTION 1: compare fromDate using a string comparison ...

IF fromDate = "0" THEN 
   SET @fromDate = '21600000'; 
ELSEIF fromDate > 0 THEN 
   ...
ELSE  
   ...
END IF;

OPTION 2: convert fromDate from varchar to int

DECLARE fromDateInt INT;

SELECT CONVERT(fromDate as SIGNED) INTO fromDateInt;
IF fromDate = 0 THEN 
   SET @fromDate = '21600000'; 
...

Cast from VARCHAR to INT - MySQL

Also, you may want to check/handle: 1. Could fromDate be a float?
2. Could fromDate be NULL?

Upvotes: 2

Related Questions