SQL Server and MySQL Ramblings

15 June 2010 - Using SELECT CASE for Insert and Update Statements

I just ran in the situation where I wanted to insert/update a field based on whether or not is different from the default value from another table.

For the current system that I am working on we are using invoice templates for each location, these templates are simply lists of fee types with default descriptions being pulled from the fee types table. A recent request was to enable the users to enter there own default invoice item descriptions when creating the templates – of course the ability to change the invoice item descriptions was already present on the invoice creation screen. So this little change required adding a new column description to the template item table. What I wanted to do was populate this new field ONLY when the users entered something other than the default description.

For example:
Fee Type: ADMIN
Default Description: Administration Fee

So, if the users leave the field blank or enter “Administration Fee” I do not want to save anything to the new template_item_table.template_item_description field and when loading the template for creating a new invoice I want to simply pull the default description from the existing fee type table. This way, if the system wide default ever changes, all those invoice templates created to use that default would still pickup the system wide default.

On the select side this was stright forward using ISNULL and NULLIF in combination to test for blank/empty or NULL template_item_table.template_item_description.

BUT when it came to the Insert and Update statements, I wanted to find a simple way of testing if the user has entered anything other than the default fee type description without having to do a separate look-up. Working with a colleague we came up with an inline SELECT CASE statement, which works beautifully, once we figured out the proper syntax.

The above will insert NULL to the template_item_table.template_item_description as I am trying to enter a description of “Administration Fee” and the default description will be pulled from the fee_type_table – which happens to be “Administration Fee”.

The above will insert “ Some Custom Description” into the template_item_table.template_item_description field.

Note: The biggest gotcha here was putting the other fields values inside the select.

Updating is very similar, just put the SELECT CASE after the field being updated:

That is all there was too it, now I am only inserting or updating the template_item_table.template_item_description if it is not the default value, and pull the default value unless this field over rides it.

4 April 2010 - 7 performance tips for faster SQL queries

Full article - Sean McCrown wrote a great article on performace tips for faster SQL queries on InfoWorld.com.

Sean McCrown wrote a great article on performace tips for faster SQL queries, several of them go with out saying, but clearly they need to be repeated! I have been seeing several of them rearing there ugly head on some projects recently, such as pulling all the columns from multiple tables and double dipping (even tripple dipping!).

22 August 2008 - Concatenating Row Values in Transact-SQL

Full article - Anith Sen wrote a great article on how to concatinate rows in Transact-SQL

I have had the need in the past to concatinate rows into one value and when I saw this article in Firdays SQLServerCentral newsletter, I knew it would be a good read. Anith goes into great detail, covering several approaches, even CLR for the .NET developer. Now I just need to find the need to concatinate rows again...

11 August 2008 - How to configure Database Mail in SQL Server 2005 to send mail

Full article - Roman Rehak wrote a great article on how to configure Database Mail in SQL Server 2005

Roman Rehak wrote a great article on how to configure Database Mail in SQL Server 2005 over on searchsqlserver.com. I needed to implement this a while back, and though it was straight forward to setup unlike SQL Mail, I found quite a bit of interesting information in the article and wanted to share it.

3 August 2008 - Calculate the Number of Week Days Between two Dates with T-SQL

Full article - How To Calculate the Number of Week Days Between two Dates - Jeff's SQL Server Blog.

This morning I came across an interesting post on calculating the number of week days between two dates. I have, or at least do not recall needing this requirement in the past but with all the reporting and task management systems that I am currently or foreseeable be working on, I have a strong feeling that this will come up sooner or later. The solution proposed on Jeff's SQL Server Blog is really quit simple, utilizing the DateDiff day and week properties, and if you have a holiday schedule you can exclude those dates as well. One think that I know I would need to ensure is making sure the start and end dates passed in are not weekends or holidays.

18 July 2008 - SQL Server Get Date Portion from DateTime Field

Full article - Date and Time Manipulation in SQL Server 2000

I needed to return just the date portion of a DateTime field from SQL Server and found a good article at sqljunkies.com (link above) that goes in to good depth of all the various date formatting codes that you can use. To simeply return the date portion in US format:

SELECT CONVERT(VARCHAR,DateColumn,101) AS DateColumn FROM Table

Were 101 is the format of the date you want to return, in this case mm/dd/yyyy. 104 will return the date in the English format (which always seems much more logical to me) dd/mm/yyyy. (for convenience I am including the full list of codes below).