How To Use A CASE Statement In A Select In T-SQL (SQL Server)



TODO:

Have you ever wanted to use a CASE statement in a SELECT statement in T-SQL?

 

SOLUTION:

SELECT  Id,
     CASE PrimaryColor WHEN NULL THEN 
          HeaderColor
     ELSE
          PrimaryColor
     END AS HeaderColor
     ,SectionHeader...
FROM...
WHERE...

 

NOTES:

This example will evaluate the column 'PrimaryColor'.  If it is NULL, then the column 'HeaderColor' will be used.  If it does have a value, then 'PrimaryColor' will be used.

How To Select The Entire Contents Of A Varchar(max) Or NVarchar(max) Column In SQL Server Management Studio



TODO:

Have you ever wanted to select a varchar(max), or nvarchar(max) column in SSMS?  You will notice that the data is truncated when you have a large amount of data stored in the column.  To get around this we will convert the string to XML, then save the results to an XML file, so you can open in your favorite XML editor / viewer.

 

SOLUTION:

 

--to select a column with NON-XML data
select convert(xml,'<xml><![CDATA[' + cast(LogData as varchar(max)) + ']]></xml>') FROM LogTable where Id = 1234

--to select a column with XML data
select convert(xml, LogData) FROM LogTable where Id = 1234

 

 

NOTES:

The first example wraps the data in CDATA tags, so that it will be well-formed XML. 

The second example does not, because I am storing well-formed XML in the column already.