0

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.

  • What do you want to have happen when no column is NULL? – Aaron Bertrand Mar 12 at 14:54
3

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;

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.