Display all fields with null values in a particular row in SQL Server

Question

I have a requirement where I want to fetch and display all the fields with NULL value for a particular row in the table.

Some thing like:

select 'all columns with NULL' 
   from table_xyz 
   where primary_key='pk_row2';

I use SQL Server 2012. Please help achieve this.

Answer 1

SQL as a language allows you to easily dictate which rows to include in output, but not which columns. For this you'll need dynamic SQL (and a consuming app which is equally dynamic). An example, given this table and data:

USE tempdb;
GO

CREATE TABLE dbo.blat(pk varchar(32) NOT NULL, a int, b int,
  CONSRAINT pk_blat PRIMARY KEY (pk));

INSERT dbo.blat(pk,a,b) VALUES
  ('key 1',1,NULL),
  ('key 2',1,1),
  ('key 3',NULL,1),
  ('key 4',NULL,NULL);

One solution:

-- input param:
DECLARE @pk varchar(32) = 'key 4';


DECLARE @sql nvarchar(max) = N'SELECT pk';

SELECT @sql += 
    CASE WHEN a IS NULL THEN N',a' ELSE N'' END
  + CASE WHEN b IS NULL THEN N',b' ELSE N'' END
FROM dbo.blat WHERE pk = @pk;

SET @sql += N' FROM dbo.blat WHERE pk = @pk;';

EXEC sys.sp_executesql @sql, N'@pk varchar(32)', @pk;

Try it with all four combinations. Don't forget to clean up:

DROP TABLE dbo.blat;