MySQL - checking for NULL and dynamic SQL inside a stored procedure

Question

DROP PROCEDURE IF EXISTS HaveSomeFun; CREATE PROCEDURE HaveSomeFun(user_id CHAR(50),house_id CHAR(50),room_id CHAR(50),fun_text TEXT,video_url CHAR(100)) BEGIN DECLARE query_full TEXT; SET @fields_part = 'INSERT INTO fun(FunKey,UserKey,FunBody,LastModified'; SET @values_part = CONCAT(') VALUES( NewBinKey(), KeyToBin(\"', user_id, '\"), \"', fun_text, '\", NOW() '); IF (house_id) THEN SET @fields_part = CONCAT(@fields_part, ', HouseKey'); SET @values_part = CONCAT(@values_part, ', KeyToBin(\'', house_id, '\')'); END IF; IF (room_id) THEN SET @fields_part = CONCAT(@fields_part, ', RoomKey'); SET @values_part = CONCAT(@values_part, ', KeyToBin(\'', room_id, '\')'); END IF; IF (video_url IS NOT NULL) THEN SET @fields_part = CONCAT(@fields_part, ', VideoURL'); SET @values_part = CONCAT(@values_part, ', "', video_url, '"'); END IF; SET query_full = CONCAT(@fields_part, @values_part, ' );'); SET @query_full = query_full; PREPARE STMT FROM @query_full; EXECUTE STMT; SELECT query_full; END; 

and

 CALL HaveSomeFun('29B455DE-A9BC-102D-9C16-00163EEDFCFC', '', 'F82C47A8-64DE-11DF-9D7E-0026B9481364', 'Jokes apart', ''); 

will build the next line in the query_full variable

 INSERT INTO fun(FunKey,UserKey,FunBody,LastModified, VideoURL) VALUES( NewBinKey(), KeyToBin("29B455DE-A9BC-102D-9C16-00163EEDFCFC"), "Jokes apart", NOW() , "" ); 

But I need to get

 INSERT INTO fun(FunKey,UserKey,FunBody,LastModified, RoomKey, VideoURL) VALUES( NewBinKey(), KeyToBin("29B455DE-A9BC-102D-9C16-00163EEDFCFC"), "Jokes apart", NOW() , KeyToBin('F82C47A8-64DE-11DF-9D7E-0026B9481364'), "" ); 

Something is missing on the IF (room_id) THEN check. But I can not impose IF (room_id IS NOT NULL) THEN , since it will create a KeyToBin (''), and RoomKey is a foreign key, KeyToBin ('') will create an invalid RoomKey.

Any idea?

Answer 1

You have a problem, it should be like

 DROP PROCEDURE IF EXISTS HaveSomeFun; CREATE PROCEDURE HaveSomeFun(user_id CHAR(50),house_id CHAR(50),room_id CHAR(50),fun_text TEXT,video_url CHAR(100)) BEGIN DECLARE query_full TEXT; SET @fields_part = 'INSERT INTO fun(FunKey,UserKey,FunBody,LastModified'; SET @values_part = CONCAT(') VALUES( NewBinKey(), KeyToBin(\"', user_id, '\"), \"', fun_text, '\", NOW() '); IF (house_id != '') THEN SET @fields_part = CONCAT(@fields_part, ', HouseKey'); SET @values_part = CONCAT(@values_part, ', KeyToBin(\'', house_id, '\')'); END IF; IF (room_id != '') THEN SET @fields_part = CONCAT(@fields_part, ', RoomKey'); SET @values_part = CONCAT(@values_part, ', KeyToBin(\'', room_id, '\')'); END IF; IF (video_url IS NOT NULL) THEN SET @fields_part = CONCAT(@fields_part, ', VideoURL'); SET @values_part = CONCAT(@values_part, ', "', video_url, '"'); END IF; SET query_full = CONCAT(@fields_part, @values_part, ' );'); SET @query_full = query_full; PREPARE STMT FROM @query_full; EXECUTE STMT; SELECT query_full; END;