MySQL Forums
Forum List  »  Stored Procedures

Problem using CAST() in stored procedures and functions
Posted by: Robert Hairgrove
Date: April 26, 2021 06:17AM

Using MySQL Community Server 5.7.33 on Linux Ubuntu 18.04.

If I issue the SQL statement "SELECT CAST('1,2,3' AS SIGNED);" it gives me
the expected result (1) with the warning #1292 "Truncated incorrect INTEGER value: '1,2,3'".

-- mysql> SELECT CAST('1,2,3' AS SIGNED);
-- +-------------------------+
-- | CAST('1,2,3' AS SIGNED) |
-- +-------------------------+
-- | 1 |
-- +-------------------------+
-- 1 row in set, 1 warning (0.00 sec)
--
-- mysql> SHOW WARNINGS;
-- +---------+------+--------------------------------------------+
-- | Level | Code | Message |
-- +---------+------+--------------------------------------------+
-- | Warning | 1292 | Truncated incorrect INTEGER value: '1,2,3' |
-- +---------+------+--------------------------------------------+
-- 1 row in set (0.00 sec)

But if I use the CAST() function in a stored procedure or function, I get an error. When I ignore the error by declaring a CONTINUE handler, I get no error or warning, but the result is NULL.

Is there a good workaround for this?

Here is my test setup:

mysql> CREATE DATABASE IF NOT EXISTS test_db CHARACTER SET=utf8 COLLATE=utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> USE test_db;
Database changed
mysql> DELIMITER $$
mysql> CREATE FUNCTION fn_cast_test(v_input VARCHAR(255)) RETURNS INTEGER DETERMINISTIC
-> BEGIN
-> DECLARE v_retval INTEGER DEFAULT 0;
-> DECLARE CONTINUE HANDLER FOR 1292 BEGIN END;
-> SET v_retval := CAST(v_input AS SIGNED);
-> RETURN v_retval;
-> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT fn_cast_test('1,2,3');

-- Results:
-- +-----------------------+
-- | fn_cast_test('1,2,3') |
-- +-----------------------+
-- | NULL |
-- +-----------------------+
-- 1 row in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem using CAST() in stored procedures and functions
574
April 26, 2021 06:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.