2
\$\begingroup\$

Let assume a table has more than 100 of columns & I want to display all the rows when all of its columns (Col1, Col2, ..., ColN) are nulls except for the column(s) I mentioned (PK).

This is just a sample table I created to explain this question.

Sample Table with data

+----+------+------+------+------+
| PK | Col1 | Col2 | Col3 | Col4 |
+----+------+------+------+------+
|  1 | 45   | 12   | 171  | 15   |
|  2 | 13   | NULL | 15   | NULL |
|  3 | 17   | 14   | 130  | NULL |
|  4 | 10   | 11   | 110  | 12   |
|  5 | 45   | 15   | 185  | 54   |
|  6 | NULL | NULL | NULL | NULL |
|  7 | 16   | 48   | NULL | 25   |
|  8 | NULL | NULL | NULL | NULL |
|  9 | 13   | 45   | 41   | 64   |
| 10 | NULL | NULL | NULL | NULL |
| 11 | 21   | 12   | 165  | 56   |
| 12 | NULL | NULL | NULL | NULL |
+----+------+------+------+------+

Image: https://i.stack.imgur.com/z17GK.png

In this case, 6, 8, 10 and 12 rows (Column name: PK) are the result I want to see since all of the columns (Col1 to Col4) are null except for the PK column.

Desired Output

+----+------+------+------+------+
| PK | Col1 | Col2 | Col3 | Col4 |
+----+------+------+------+------+
|  6 | NULL | NULL | NULL | NULL |
|  8 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 12 | NULL | NULL | NULL | NULL |
+----+------+------+------+------+

Image: https://i.stack.imgur.com/prwFo.png

The typical way to do this is to write a query like this.

SELECT * FROM TableName WHERE COALESCE(Col1, Col2, Col3, Col4) is null

If the above table has more 100 columns as I said, writing this query will be hard especially if you have to do it often.

Therefore, I have created a query like this.

DECLARE @query NVARCHAR(MAX);

SELECT @query = ISNULL(@query+', ','') + [name] 
                FROM  sys.columns 
                WHERE object_id = OBJECT_ID('TableName') 
                AND  [name] != 'PK';

SET @query = N'SELECT * FROM TableName WHERE COALESCE('+ @query +') IS NULL';

EXECUTE(@query)

Does this SQL code follow common best practices? How is the quality of this code? (Ex: Performance)

\$\endgroup\$
  • 2
    \$\begingroup\$ The code you presented in its current form is not meaningfully reviewable. We only review real, working code. If you edit your question to contain your actual code we can review it for improvements. See What topics can I ask about? for reference. \$\endgroup\$ – Phrancis Apr 18 '18 at 23:21
  • \$\begingroup\$ @Phrancis, This is a working code (at least for me). I have written this query to answer a question in StackOverflow. Now I'm having doubt because many experts have recommended the first query even when there are many columns. So, I like to show this query to an expert and get their expertise before I recommend this query to someone else. \$\endgroup\$ – DxTx Apr 19 '18 at 6:38
  • 1
    \$\begingroup\$ @Phrancis We have reviewed TSQL code before. \$\endgroup\$ – paparazzo Apr 19 '18 at 17:44
  • 1
    \$\begingroup\$ Looks good to me \$\endgroup\$ – paparazzo Apr 19 '18 at 17:50
1
\$\begingroup\$

Does this SQL code follow common best practices?

No.

SET @query = N'SELECT * FROM TableName WHERE COALESCE('+ @query +') IS NULL';

1) It is common best practice to not use 'Select *' in production code. One's apologetics might include 'Well, this is dynamic code ...' and if you're okay with that (or the customer is) then carry on.

2) I would use a Socratic method and ask what is the purpose of returning columns you know are null? Why wouldn't you write the query to only return the PK if all other columns are null?

How is the quality of this code?

It looks is fine.

I would suggest if you want a better evaluation, to provide sample objects / DDL to allow others to run it locally. Perhaps to evaluate the query plan.

\$\endgroup\$
  • 1
    \$\begingroup\$ I see no problem with select * here. It is built up based on all columns. \$\endgroup\$ – paparazzo Apr 20 '18 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.