MySQL: stored proc that returns rows from table with NULLs in any column

Hi Experts,

A complicated problem: I need a stored procedure FindNulls which gets passed the name of a table, then returns the ids of records that have a NULL value in any column of that table.  The only column name common to all tables is id; the proc won't know the other column names ahead of time, although of course they can be found in `INFORMATION_SCHEMA`.`COLUMNS`

For example, given these 2 test tables and data:

create table TestA (id int, column1 int, column2 varchar(10));
insert into TestA values(1,17,'hello'),(2,NULL,'goodbye'),(3,14,NULL);
create table TestB (id int, someColumn int, someOtherColumn decimal(4,2));
insert into TestB values(1,5,NULL),(2,NULL,16.52),(3,88,44.44);

call FindNulls('TestA') /*should return 2 and 3 */
call FindNulls('TestB') /*should return 1 and 2 */
EffinGoodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
It's a little convoluted, but it works.  Assumes the table is in the same database as you are currently connected to.

DELIMITER $$

DROP PROCEDURE IF EXISTS findnulls$$
CREATE PROCEDURE findnulls (
	in_table_name VARCHAR(100)
	, OUT nullcols VARCHAR(1000)
)
READS SQL DATA
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE cur_field VARCHAR(100);
	DECLARE fieldlist_cur CURSOR FOR
		SELECT fieldname
		FROM collist;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	CREATE TEMPORARY TABLE collist (fieldname VARCHAR(100));
	CREATE TEMPORARY TABLE nullcollist (fieldname VARCHAR(100));
	
	SET @FETCH_COL_SQL = CONCAT("INSERT INTO collist SELECT column_name "
					  , "FROM information_schema.columns "
					  , "WHERE TABLE_NAME = '", in_table_name, "'"
					  , " AND TABLE_SCHEMA = '", DATABASE(), "'"
					  , " AND IS_NULLABLE = 'YES'"
					  );
	PREPARE fetch_col_sql FROM @FETCH_COL_SQL;
	EXECUTE fetch_col_sql;
	DEALLOCATE PREPARE fetch_col_sql;


	OPEN fieldlist_cur;
	fieldloop: LOOP
		FETCH fieldlist_cur INTO cur_field;
        IF (done=1) THEN
			LEAVE fieldloop;
		END IF;
									
		SET @NULL_COUNT_SQL = CONCAT("SELECT COUNT(1) INTO @nullcount"
								 , " FROM ", in_table_name
								 , " WHERE ", cur_field, " IS NULL");
		PREPARE sql2 FROM @NULL_COUNT_SQL;
		EXECUTE sql2;
		DEALLOCATE PREPARE sql2;

		IF (@nullcount > 0)
		THEN
			INSERT INTO nullcollist VALUES (cur_field);
		END IF;
		
	END LOOP fieldloop;
	
	close fieldlist_cur;

	SELECT GROUP_CONCAT(fieldname) INTO nullcols FROM nullcollist;

	DROP TABLE collist;
	DROP TABLE nullcollist;
END$$

DELIMITER ;

CALL findnulls("table_name", @output);
SELECT @output;

Open in new window

0
EffinGoodAuthor Commented:
Hi nemws1,

Thank you, but that's not what I'm after. Your procedure returns the names of columns that contain at least 1 null value; I want the ids of ROWS that have at least 1 column with a null value.

For example, with your procedure,

CALL findnulls("TestA", @output);
SELECT @output;

I get:
|-------------------------|
|@output                |
|-------------------------|
| column1,column2 |
|-------------------------|

When what I want is:
|-------------------------|
|@output                |
|-------------------------|
| 2                            |
|-------------------------|
| 3                           |
|-------------------------|
0
nemws1Database AdministratorCommented:
Drrp. Yes, I read that, but by the end forgot that's what you wanted. I'll modify and post soon.
0
nemws1Database AdministratorCommented:
Here you are.

DELIMITER $$

DROP PROCEDURE IF EXISTS findnulls$$
CREATE PROCEDURE findnulls (
	in_table_name VARCHAR(100)
)
READS SQL DATA
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE cur_field VARCHAR(100);
	DECLARE fieldlist_cur CURSOR FOR
		SELECT fieldname
		FROM collist;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	CREATE TEMPORARY TABLE collist (fieldname VARCHAR(100));
	CREATE TEMPORARY TABLE nullcollist (id INT);
	
	SET @FETCH_COL_SQL = CONCAT("INSERT INTO collist SELECT column_name "
					  , "FROM information_schema.columns "
					  , "WHERE TABLE_NAME = '", in_table_name, "'"
					  , " AND TABLE_SCHEMA = '", DATABASE(), "'"
					  , " AND IS_NULLABLE = 'YES'"
					  );
	PREPARE fetch_col_sql FROM @FETCH_COL_SQL;
	EXECUTE fetch_col_sql;
	DEALLOCATE PREPARE fetch_col_sql;


	OPEN fieldlist_cur;
	fieldloop: LOOP
		FETCH fieldlist_cur INTO cur_field;
        IF (done=1) THEN
			LEAVE fieldloop;
		END IF;
									
		SET @NULL_COUNT_SQL = CONCAT("INSERT INTO nullcollist"
						 , " SELECT id"
						 , " FROM ", in_table_name
						 , " WHERE ", cur_field, " IS NULL");
		PREPARE sql2 FROM @NULL_COUNT_SQL;
		EXECUTE sql2;
		DEALLOCATE PREPARE sql2;
		
	END LOOP fieldloop;
	
	close fieldlist_cur;

	SELECT DISTINCT id FROM nullcollist ORDER BY id;

	DROP TABLE collist;
	DROP TABLE nullcollist;
END$$

DELIMITER ;

CALL findnulls("users");

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EffinGoodAuthor Commented:
Thanks! Works like a charm!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.