CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Robert
Robert

Reputation: 666

MS sql Case Statement

I have this select statement and what i am trying to accomplish is when AuditLog is null then set 'Enabled' but my issues is i am not sure how to handle it when it is not NULL, i want to be able to get the data from AuditLog into AuditLogEnabled when it is not null. I have tried adding an else, but have not been able to get it to work. can someone show me the correct way to write this.

select FirstName,AuditLog,
CASE WHEN AuditLog IS NULL THEN 'Enabled' END AS AuditLogEnabled
from UserDetail
where FirstName = 'test'

Upvotes: 2

Views: 124

Answers (3)

AgentSQL
AgentSQL

Reputation: 2940

I would recommend doing it using COALESCE - 

SELECT FirstName,AuditLog,COALESCE(AuditLog,'Enabled') AS AuditLogEnabled
FROM UserDetail
WHERE FirstName = 'test'

Reason for using COALESCE - ISNULL is dependent to data type of the first argument. And sometimes it leads to data truncation - For Example - 

DECLARE @value VARCHAR(5)
SELECT 'Using COALESCE', COALESCE(@value, 'My String')
SELECT 'Using ISNULL', ISNULL(@value, 'My String')

Result - 

Using COALESCE  My String
Using ISNULL    My St

This could be serious problem. Don't use ISNULL unless you are sure about datatype and its length.

Upvotes: 3

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

You can do it with a case, but in this simple situation, I prefer ISNULL()

select FirstName,AuditLog,ISNULL(AuditLog,'Enabled') AS AuditLogEnabled
from UserDetail
where FirstName = 'test'

Upvotes: 3

thursdaysgeek
thursdaysgeek

Reputation: 7946

As long as AuditLog is a string, then something like this should work:

select FirstName,
CASE WHEN AuditLog IS NULL THEN 'Enabled' ELSE AuditLog END AS AuditLogEnabled
from UserDetail
where FirstName = 'test'

Upvotes: 1

Related Questions