Hello

I have a stored procedure in EDB 2.12 Unicode that takes a parameter and converts it to upper case.

EXECUTE IMMEDIATE 'UPDATE Client SET FirstN = UPPER(?), LastN = UPPER(?) WHERE ClientID = ?'

The parameters are  (IN "FirstN" VARCHAR COLLATE UNI, IN "LastN" VARCHAR COLLATE UNI, ...

If either parameter is NULL then the updated field has an empty string. I also use the name parameters elsewhere in the SP, so if I were to convert the values to upper case in Delphi I would need additional parameters.

How can I make UPPER work with a NULL value?

Regards

Peter

Hi Peter,

EXECUTE IMMEDIATE 'UPDATE Client SET FirstN = UPPER(COALESCE(?, '''')),
LastN = UPPER(COALESCE(?, '''')) WHERE ClientID = ?'

---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------

Terry

Thanks for that. I could hear COALESCE knocking on the door of my brain, but forgot the syntax. I use it elsewhere of course, but it is difficult to troll through all the stored procedures to see a previous example. I should save them all as text files.

Regards

Peter

I couldn't use COALESCE, because in the example Terry provided it always returned an empty string. I tried COALESCE(UPPER(?), NULL) but UPPER always returns a string, so that was a bust. I could have wrapped that in another COALESCE but an IF statement appeared simpler...

'UPDATE Client SET FirstN = IF(NOT ? IS NULL, UPPER(?), NULL),
LastN = IF(NOT ? IS NULL, UPPER(?), NULL) WHERE ClientID = ?'
USING FirstN, FirstN, LastN, LastN, ClientID;

...and now I get a NULL value where there should be one.

Maybe I missed something, but thanks anyway.

Regards

Peter

Peter

I am trying the following and it seems to work fine (in elevate dbmanager 2.12) as long as clientid is found!  If a parameter is null the field becomes null, if it is an empty string it becomes an empty string and if it is a string it becomes the upper case of the string.   

BEGIN
execute immediate 'UPDATE Client SET FirstN = upper(?),
LastN = upper(?) WHERE ClientID = ?'
USING FirstN, LastN, ClientID;
END

John

I tested your code, and of course, it worked. However, when I plugged that back into the SP that edits about 50 fields at the same time, it always resulted in an empty string. I had to revert to the IF statement.

In the production SP the same IN params are reused a couple of times; the SP has an IF..END that says that IF a certain boolean IN parameter is true then the SP executes an INSERT on the ClientName table. The value is always recorded correctly in the INSERT (no change of case here), but the next statement is an UPDATE on the Client table - the case in point, if you will pardon the pun.

It is as though the parameters are altered from NULL to '' during the life of the SP. The in-line IF NULL fixes it though.

I cannot see why it is happening, but I suspect that I will have a eureka moment in a few weeks

Thanks all

Regards

Peter