SQL Blog of Zach Stagers

www.scratchbox.co.uk

SQL If Else Clause

November 24th, 2010 Posted in SQL
Nov, 24 2010
No Comments »

Frequently used in Stored Procedures, the SQL If can be used to help control the flow and execution of your SQL.

The If checks to see if a boolean statement is true, and if it is, the code directly beneath is executed. This is handy, as we often need to execute completely different code depending on the value of a variable.

The syntax is easy to use, but can make a big difference to your code:

SELECT @Value = COUNT(*) FROM SpecialOffers WHERE Valid = 1
IF (@Value > 0)
  BEGIN
    SELECT * FROM SpecialOffersProducts
  END
  ELSE
    SELECT * FROM Products

The above code is counting the number of valid special offers and assigning the number to the variable @Value.

The If then checks that @Value is greater than zero, meaning there are valid special offers available. If the check is true, and there are special offers available, our SpecialOffersProducts will be returned. If the count returns as zero, the else will be executed, and the Products will be returned.

The above example would typically be used within a stored procedure

Multiple conditions can be specified within the If with the use of the AND and OR keywords.

, ,
Comment

Case Statements

November 8th, 2010 Posted in SQL
Nov, 08 2010
No Comments »

Case Statements are used to output different data depending on the value returned from a column:

CASE
WHEN Column1 = 'Seven' THEN 7
WHEN Column1 = 'Eight' THEN 8
ELSE 0
END AS [Number]

The above syntax would be used within a select statement, and would be placed along side all other columns in your column list. This example would check all data returned from Column1, if it finds the word ‘Seven’ then the number 7 will be returned in its place, the same goes for ‘Eight’ and 8. Anything that does not equal ‘Seven’ or ‘Eight’ will be returned as a 0.

As many WHEN lines can be written as are necessary.

It’s possible to nest Case statements within each other up to a maximum of 10 levels deep.

Case Statements can also be used within Where clauses, which is particulary useful within a parameterized stored procedure.

For example, if you had a parameter of @Cost, then your Where clause may look like this:

WHERE
CASE
WHEN @Cost = 'All' THEN 1
WHEN @Cost = 'None' AND Column IS NULL THEN 1
WHEN @Cost NOT IN ('All, 'None') AND Column = CONVERT(INT, @Cost) THEN 1
ELSE 0
END = 1

The above example is checking the value of our Varchar parameter @Cost, then performing any search functionality that goes along with it.

When @Cost is All, there is no other search critiria written as we want to return everything. When @Cost is None, then we only want to return columns with a NULL entry, so we check that @Cost is ‘None’ and our Column IS NULL.

When we want to work with the @Cost parameter as an INT, we have to explicitly define that it’s not All or None, then specify our search “Column = Covert(INT, @Cost)”. If we do not explicitly define that we are no longer searching for All or None within @Cost, an error will occur for trying to compare an Int and a Varchar value.

The last use of the CASE statement that I’d like to mention is within an UPDATE statement. You can use the CASE to define what value should be set depending on the value of another column, for example:

UPDATE Table
SET Column = CASE WHEN Column2 = 'Seven' THEN 7
WHEN Column2 = 'Eight' THEN 8
END
WHERE CLAUSE

, , ,
Comment

Order By Clause – Returning a Random Result Set

November 7th, 2010 Posted in SQL
Nov, 07 2010
No Comments »

The Order By Clause is generally used to make data sets a little tidier, but a lesser known fact is that it can also be used to return a random set of data!

An Order By will always be placed at the very end of your SQL statement:

SELECT * FROM People
ORDER BY FirstName, SecondName ASC

The above statement will order the result set by FirstName in ascending (Smallest to Largest, A-Z) order, then by SecondName. For example, John Adams would be ahead of John Smith in the result set.

If you would like to order the result set from Largest to Smallest or Z-A, simply add DESC (For Descending) in place of ASC.

You don’t have to specify ASC or DESC at the end, but in not doing so, SQL Server will assume the default behaviour which is ascending.

The ORDER BY can also be used to specify that you would like to return a random result set. This is usually used in conjunction with a TOP Clause:

SELECT TOP 10 * FROM Person
ORDER BY NEWID()

The above will select the top 10 rows from within the Person table, but because we have specified ORDER BY NewId(), we’ll return a different set of 10 rows each time the query is run.

, ,
Comment

Update Statements with Aliases

November 3rd, 2010 Posted in SQL
Nov, 03 2010
1 Comment »

I’ve heard serveral people claim that you cannot use aliases within an Update statement. This is a complete lie, you can!

The alias simply replaces the table name:

UPDATE dbo.Products
SET dbo.Products.BuyerName = dbo.Person.PersonName
FROM dbo.Person JOIN
     dbo.Products ON dbo.Person.PersonId = dbo.Products.PersonId
WHERE dbo.Person.PersonId = 4

Becomes:

UPDATE Pr
SET Pr.BuyerName = 'Zach Stagers'
FROM Person P JOIN
     Products Pr ON Pr.PersonId = P.PersonId
WHERE P.PersonId = 4

Easy!

, ,
Comment

DateAdd System Function

November 1st, 2010 Posted in SQL
Nov, 01 2010
No Comments »

The DATEADD system function is incredible, it makes working with dates so versatile and easy! Knowing how to use this function will make your life easier when you need to do some calculations with dates.

The function allows you to add or subtract days, months, or years from a date. For example, if you were writing a dynamic report which will need to be run between the start of the current financial year and the current date, you can work out the current year and month, and go from there to work out the start of the current financial year without needing to hardcode the value, then re-visiting your code a year on to update the value!

The syntax is easy to master:

DATEADD ( Interval, Number, Date )

Replace Interval with which ever element of the date you want to work with (DAY, MONTH, or YEAR). Replace Number with the amount you’d like to add or subtract. To subtract pass in a negative value (-1). Replace Date with the date expression that you are working with.

This is easy enough, but what makes this one a little trickier to use is that in can be nested within other DATEADD function calls. To do this, you simply pass in the DATEADD function as the DATE.

DATEADD ( Interval, Number, DATEADD ( Interval, Number, Date ) )

The function can also be used with other date functions, such as DateName, DatePart, and DateDiff. I will post on these another time.

Going back to my financial year example, below is one way to find the current financial year, and shows a slightly more advanced use of the function in conjunction with a few other system functions.

CASE WHEN GETDATE() BETWEEN

CONVERT(DATE, DATEADD(D, 1, DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 1, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE())))))

AND

CONVERT(DATE, DATEADD(D, 1, DATEADD(M, -1, DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 5, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE()))))))

THEN CONVERT(DATE,DATEADD(YEAR, -1, DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 5, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE())))))

<code>ELSE CONVERT(DATE,DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 5, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE()))))

END
, ,
Comment