Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

How To Check If Any, ALL Or No Parameters Have A NULL Value

From Wiki

Jump to: navigation, search

Let's say you have a procedure that accepts 6 parameters. depending on if these parameters are all null, all not null, some null and some not null you want to do different things. How can you test this the easiest? This depends what you are testing for but COALESCE and concatenation are the easiest (read least to type) to do.


So let's start with all nulls You can do a bunch of ANDs You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL You can also union the parameters and do a NOT EXIST NOT NULL test Finally you can use COALESCE which will return NULL if ALL the values are NULL


tsqlLine number Off | Hide | Select all
  1. --ALL NULL
  2.     DECLARE @Param1 VARCHAR(10),
  3.     @Param2 VARCHAR(10),
  4.     @Param3 VARCHAR(10),
  5.     @Param4 VARCHAR(10),
  6.     @Param5 VARCHAR(10),
  7.     @Param6 INT
  8.  
  9.  
  10.     IF @Param1 IS NULL
  11.     AND @Param2 IS NULL
  12.     AND @Param3 IS NULL
  13.     AND @Param4 IS NULL
  14.     AND @Param5 IS NULL
  15.     AND @Param6 IS NULL
  16.     PRINT 'ALL NULL'
  17.  
  18.     SET CONCAT_NULL_YIELDS_NULL OFF
  19.     IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
  20.     PRINT 'ALL NULL'
  21.  
  22.  
  23.     IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
  24.     SELECT @Param2 UNION ALL
  25.     SELECT @Param3 UNION ALL
  26.     SELECT @Param4 UNION ALL
  27.     SELECT @Param5 UNION ALL
  28.     SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
  29.     PRINT 'ALL NULL'
  30.  
  31.  
  32.  
  33.     IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NULL
  34.     PRINT 'ALL NULL'


Next up is to test that at least one value is not NULL You can do a bunch of Ors You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL You can also union the parameters and do a EXIST NOT NULL test Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL

tsqlLine number Off | Hide | Select all
  1. --Some Non Nulls
  2.     DECLARE @Param1 VARCHAR(10),
  3.     @Param2 VARCHAR(10),
  4.     @Param3 VARCHAR(10),
  5.     @Param4 VARCHAR(10),
  6.     @Param5 VARCHAR(10),
  7.     @Param6 INT
  8.  
  9.     SELECT @Param1 ='A'
  10.  
  11.     SET CONCAT_NULL_YIELDS_NULL OFF
  12.     IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
  13.     PRINT 'At least One Value is not NULL'
  14.  
  15.  
  16.  
  17.     IF @Param1 IS NOT NULL
  18.     OR @Param2 IS NOT NULL
  19.     OR @Param3 IS NOT NULL
  20.     OR @Param4 IS NOT NULL
  21.     OR @Param5 IS NOT NULL
  22.     OR @Param6 IS NOT NULL
  23.     PRINT 'At least One Not NULL'
  24.  
  25.  
  26.     IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
  27.     SELECT @Param2 UNION ALL
  28.     SELECT @Param3 UNION ALL
  29.     SELECT @Param4 UNION ALL
  30.     SELECT @Param5 UNION ALL
  31.     SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
  32.     PRINT 'At least One Not NULL'
  33.  
  34.     IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NOT NULL
  35.     PRINT 'At least One Not NULL'


Next up is to test that there are no NULLs You can do a bunch of ANDs You can also union the parameters and do a NOT EXIST NULL test Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL

tsqlLine number Off | Hide | Select all
  1. --No NULLS
  2.     DECLARE @Param1 VARCHAR(10),
  3.     @Param2 VARCHAR(10),
  4.     @Param3 VARCHAR(10),
  5.     @Param4 VARCHAR(10),
  6.     @Param5 VARCHAR(10),
  7.     @Param6 INT
  8.  
  9.     SELECT @Param1 ='A',
  10.     @Param2 ='A',
  11.     @Param3 ='A',
  12.     @Param4 ='A',
  13.     @Param5 ='A',
  14.     @Param6 =1
  15.  
  16.  
  17.     IF @Param1 IS NOT NULL
  18.     AND @Param2 IS NOT NULL
  19.     AND @Param3 IS NOT NULL
  20.     AND @Param4 IS NOT NULL
  21.     AND @Param5 IS NOT NULL
  22.     AND @Param6 IS NOT NULL
  23.     PRINT 'No NULLs'
  24.  
  25.     IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
  26.     SELECT @Param2 UNION ALL
  27.     SELECT @Param3 UNION ALL
  28.     SELECT @Param4 UNION ALL
  29.     SELECT @Param5 UNION ALL
  30.     SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
  31.     PRINT 'No NULLs'
  32.  
  33.  
  34.     SET CONCAT_NULL_YIELDS_NULL ON
  35.     IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
  36.     PRINT 'No NULLs'


The final test is to test that at least one value is NULL You can do a bunch of Ors You can concatenate into a string and leave the default CONCAT_NULL_YIELDS_NULL ON because CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL You can also union the parameters and do a EXIST NOT NULL test

tsqlLine number Off | Hide | Select all
  1. --Some Nulls
  2.     DECLARE @Param1 VARCHAR(10),
  3.     @Param2 VARCHAR(10),
  4.     @Param3 VARCHAR(10),
  5.     @Param4 VARCHAR(10),
  6.     @Param5 VARCHAR(10),
  7.     @Param6 INT
  8.  
  9.  
  10.     SELECT @Param1 ='A',
  11.     @Param2 ='A',
  12.     @Param3 ='A',
  13.     @Param4 =null,
  14.     @Param5 ='A',
  15.     @Param6 =1
  16.  
  17.     IF @Param1 IS NULL
  18.     OR @Param2 IS NULL
  19.     OR @Param3 IS NULL
  20.     OR @Param4 IS NULL
  21.     OR @Param5 IS NULL
  22.     OR @Param6 IS NULL
  23.     PRINT 'At least One NULL'
  24.  
  25.     SET CONCAT_NULL_YIELDS_NULL ON
  26.     IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
  27.     PRINT 'At least One NULL'
  28.     ELSE
  29.     PRINT 'No NULLs'
  30.  
  31.     IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
  32.     SELECT @Param2 UNION ALL
  33.     SELECT @Param3 UNION ALL
  34.     SELECT @Param4 UNION ALL
  35.     SELECT @Param5 UNION ALL
  36.     SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
  37.     PRINT 'At least One NULL'


Contributed by: --SQLDenis 16:24, 30 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section NULLS

115 Rating: 2.4/5 (10 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/How_To_Check_If_Any%2C_ALL_Or_No_Parameters_Have_A_NULL_Value"

Powered by MediaWiki GNU Free Documentation License 1.2
Valid XHTML | Valid CSS