[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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 */
0
EffinGood
Asked:
EffinGood
  • 3
  • 2
1 Solution
 
nemws1Commented:
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
 
nemws1Commented:
Drrp. Yes, I read that, but by the end forgot that's what you wanted. I'll modify and post soon.
0
 
nemws1Commented:
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
 
EffinGoodAuthor Commented:
Thanks! Works like a charm!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now