passing an optional char parm to an SQL procedure. Default value is ' '.
I use the optional parm in the WHERE clause of a SELECT statement.

WHERE inEffDate = ' ' or date(inEffDate) = a.effDate

I am expecting SQL to first test if the parameter is blank. If it is,
satisfy the WHERE clause and not run the data function. But the code is
bombing with the "syntax of date value not valid" error.

Can I count on SQL to not evaluate the OR part of the WHERE clause when the
first condition evaluates to true?

here is the SQL statement. This fails with the syntax of date value error.

select COUNT(*)
INTO VCOUNT
from prugadm
a
where inEffDate = ' ' or date(inEffDate) = a.effDate ;

I rewrote it to make sure the input parameter was blanks, and it is:

select COUNT(*), case when inEffDate = ' ' then 'X'
else 'Y' end result
INTO VCOUNT, vResult
from prugadm a
where inEffDate = ' ' or
date(case when inEffDate = ' ' then '2018-04-01'
else inEffDate end) = a.effdate
fetch first row only ;

The complete SQL procedure:

CREATE or replace PROCEDURE prugadm_test(
in inEffDate char(10) default ' '
)
LANGUAGE SQL
BEGIN
declare vSqlCode decimal(5,0) ;
declare sqlCode int DEFAULT 0 ;

declare vSqlState char(5) ;
declare vErrText char(256) ;
declare sqlState char(5) ;
declare vCount int default 0 ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
begin
SET vSqlCode = SQLCODE ;
SET vSqlState = SQLstate ;
get diagnostics exception 1 vErrText = message_text ;
end ;

select COUNT(*)
INTO VCOUNT
from prugadm
a
where inEffDate = ' ' or date(inEffDate) = a.effDate ;


END

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.