SQL Filters with Optional Parameters

Lets say, for example, you have some information on Cars that you want to filter buy either make, model, colour, or any combination of these.  I.e. if you select red cars you will get all makes and models that are red, or if you select red, Fords, you will get all models of red Fords…  You get the idea…
 
I used to spend lots of time building dynamic SQL based on whether or not each parameter was set, and then executing the SQL at the end.   If you’re reading this then you already know the drawbacks of dynamic SQL so I’ll skip over that for now.  
 
Then one day I found this ‘magic trick’, not sure how, or where, but I’m glad I did.
 
Lets take the cars example but with only one parameter (the same principle will expand to as many parameters as you have).
 
So, we have the Car table:
 
   CarID
   Make
   Model
   Colour
   CC
   FuelType
 
We want to select Cars with a certain colour, if that parameter is set, or show all if not.  Imagine that the Colour that we are looking for is passed to the @Colour variable (this will be null if no colour is passed).
 
SELECT
   CarID,
   Make,
   Model,
   Colour,
   CC,
   FuelType
FROM Car
WHERE Colour = (ISNULL(@Colour,Colour))
 
So, if @Colour has a value, then only records with this colour will be returned.  If @Colour is null then we will get all the records (as Colour always equals Colour for each record).
 
If you have multiple parameters, as in our initial example, just ‘AND’ them….
 
WHERE Colour = (ISNULL(@Colour,Colour))
   AND Make = (ISNULL(@Make,Make))
   AND Model = (ISNULL(@Model,Model))
 
So… no dynamic SQL
 
 
 
Advertisements
Posted in Programming | Tagged , , , , , | Leave a comment

SSIS Lookup Component Error: output column ‘x’ and reference column named ‘y’ have incompatible data types.

This error was driving me mad for ages, I assumed that the data types were in fact different!
 
Silly me.
 
This error is caused by changing the data type(s) of one (or more) of the underlying columns referenced by the lookup component.
 
The only way that I’ve found to fix this is to delete and re-create the lookup component. 
 
 
 
Posted in Programming | 2 Comments

IT Projects Lifecycle

IT Projects

Posted in Computers and Internet | Leave a comment

Newline in C# – rn ?

Coming originally from a VB background I was perfectly content using vbCrLf to add newlines to my strings.  Unable to find a C# equivalent, all my early C# code was scattered with ‘rn’ as a suitable replacement.
 
Works well, doesn’t it?
 
Well yes, right up until the point that you run it on a non-Windows system.  Unix, for example, uses just ‘n’ and as you can imagine this makes things look just a little bit untidy.
 
Microsoft have very helpfully provided the perfect solution:
 
Environment.NewLine
 
This handy little fella returns the newline string for the current platform.
 
For those of you who are interested the full rundown is here… Environment.NewLine
Posted in Programming | Leave a comment

Newline in C# – \r\n ?

Coming originally from a VB background I was perfectly content using vbCrLf to add newlines to my strings.  Unable to find a C# equivalent, all my early C# code was scattered with ‘\r\n’ as a suitable replacement.
 
Works well, doesn’t it?
 
Well yes, right up until the point that you run it on a non-Windows system.  Unix, for example, uses just ‘\n’ and as you can imagine this makes things look just a little bit untidy.
 
Microsoft have very helpfully provided the perfect solution:
 
Environment.NewLine
 
This handy little fella returns the newline string for the current platform.
 
For those of you who are interested the full rundown is here… Environment.NewLine
Posted in Programming | Leave a comment

Windows Live Messenger Error Code 80072745

Windows Live Messenger 8.1 Error code 80072745
 
I tried all the fixes I could find on Google, registry changes, cache flusing, etc… no effect at all…
 
Oddly it went away after I rebooted my wireless router…
 
Go figure?
 
UPDATE: 17/06/2007
 
Had the same error, rebooted the router, fixed first time…
 
 
 
 
Posted in Computers and Internet | 1 Comment

SSIS Custom Transformation Component

We have a data migration routine that is currently a set of stored procedures that pulls data from multiple databases and consolidates it in one database for reporting purposes.  My newest task… move this into SSIS.
 
One of the issues that we have with the existing databases is that dates are stored in multiple formats throughout.
 
Currently this is addressed using a udf that converts all the dates to a fixed format during consolidation.  So each date is wrapped in this function.
 
E.g.
INSERT INTO DestinationTable
  SELECT
    Field1,
    Field2,
    udf_cleandate(Table.Field) as CleanedDate
    etc…
 
Ideally I would have liked to standardise dates throughout the systems, but this is not an option.
 
After much searching I was finally pointed in the direction of creating a Custom Transformation Component. 
 
Two hours later and a few lines of C# and…
 
My Custom Transformation Component works!
 
None of this will mean much to most of my buddies…  but to summarise… today is a good day!
 
 
Posted in Programming | Leave a comment