CODEWITHSUNDEEP
sqlsql-servert-sqlnullsql-server-2014
HeadOverFeet
HeadOverFeet

Reputation: 788

Does SQL Server 2014 support functions such as ISNOTNULL()?

dont you know how to change ISNULL() to ISNOTNULL() in MS SQL? Something like IFNULL?

I need to do it in one step. 

Set @EMAILhtml = @CSS + @TEXT1 + isnull(@TEXT1,'') +  isnull(@table1,'') + isnull(@text2,'') + isnull(@table2,'')  + @TEXT3

But I need something like 

Set @EMAILhtml = @CSS + @TEXT1 + isNOTnull(@TEXT1,'') +  isnull(@table1,'') + isnull(@text2,'') + isnull(@table2,'')  + @TEXT3

Thanks.

Upvotes: 0

Views: 92

Answers (4)

HeadOverFeet
HeadOverFeet

Reputation: 788

 set @TEXT1 = (case when @table1 is null then null else @TEXT1 end)

before the final set

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

I think you've got something wrong:

ISNULL() is used to set a default value in case of NULL

DECLARE @v1 VARCHAR(10)='test';
SELECT ISNULL(@v1,'default') --returns `test`;
SET @v1=NULL;
SELECT ISNULL(@v1,'default') --returns `default`;

Checking for NULL is done with IS NULL. The opposite is IS NOT NULL 

DECLARE @v1 VARCHAR(100)=NULL;
SELECT CASE WHEN @v1 IS NULL THEN 'v1 is null' END

The third option in this context is NULLIF(), which returns NULL if a condition is fullfilled

DECLARE @v1 VARCHAR(10)='test';
SELECT NULLIF(@v1,'test'); --returns NULL, because @v1='test'

And last but not least there is COALESCE(). It will return the first non-null value of a list of paramters:

DECLARE @v1 VARCHAR(10)=NULL;
DECLARE @v2 VARCHAR(10)=NULL;
DECLARE @v3 VARCHAR(10)='test';
SELECT COALESCE(@v1,@v2,@v3); --returns 'test'

Upvotes: 2

peterboccia
peterboccia

Reputation: 159

I don't understand why are you trying to retrieve if is not null this way. The ISNULL() function help to substitute a NULL value to a default one

I suggest to try something like that:

CASE WHEN column IS NOT NULL THEN 'Hey is not null' ELSE 'Sorry, is null!' END

Hope this could help.

Upvotes: 1

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

are you referring to IS NULL and IS NOT NULL?
as in -

select * from t where x is null
select * from t where x is not null

Upvotes: 1

Related Questions