Wednesday, October 22, 2008

CASE SQL

-- CASE NULL --

-- Example 1:
CASE WHEN ss.SessionID IS NULL THEN 'No' ELSE 'Yes' END AS IsActive

-- Example 2:
(CASE WHEN v.[Name] is null then '' else v.[Name] end) as SomeName

-- Example 3:
SELECT g.GraphID,
g.Description,
u.UserGraphID,
CASE WHEN u.GraphTypeID IS NULL AND g.GraphID = 7 THEN 2 ELSE u.GraphTypeID END AS GraphTypeID,
u.IsActive

FROM Graphs g
LEFT JOIN UserGraphs u on (g.GraphID = u.GraphID)

Example 4

SELECT ID,

CASE TypeID

WHEN 0 THEN 1 -- Standard

WHEN 2 THEN 2 -- Enterprise

WHEN 1 THEN 3 -- Premium

ELSE 0 -- Unknown

END AS TheType

FROM Users

Get records created one hour ago

-- Get stored procedures created one hour ago

SELECT [name], create_date, modify_date FROM sys.objects
WHERE type = 'p'
AND create_date >= DATEADD(hour, -1, getdate())

Monday, October 20, 2008

Format Numbers

<td id="tdAmount" runat="server" visible="false"><%# String.Format("{0:$#,##0.00}", DataBinder.Eval(Container.DataItem, "Amount"))%>td>

Tuesday, October 14, 2008

Apostrophe

SELECT 'Peter''s car'

-----------
Peter's car

Sunday, October 5, 2008

Division By NULL

-- Division by NULL

SELECT (1/NULL) AS Result
SELECT (100/NULL) AS Result
SELECT (0/NULL) AS Result
SELECT (NULL/NULL) AS Result


Result
-----------
NULL

Result
-----------
NULL

Result
-----------
NULL

Result
-----------
NULL

Wednesday, October 1, 2008

NULLIF and ISNULL (Transact-SQL)

This looks interesting:

SELECT @OptionID = nullif(@OptionID, -1)

And this:

SELECT @OptionID = ISNULL(@OptionID, -1)