Saturday 8 September 2007

Move over Flash...Silverlight 1.0 Released!

Silverlight, Microsoft's latest rendering technology for HD video, interactive applications (and much more) has finally reached release version 1.0. I see it as a move by MS to overthrow Flash as the streaming video and 'widget' provider of choice. See http://silverlight.net/ for more details. The best part is that it has C# support on the client side! The Silverlight 1.0 Runtime can be downloaded from http://www.microsoft.com/silverlight/downloads.aspx. Expect to see a stack of video content and online games coming out with this technology.

Cool new image resizing technology - Seam Carving Rocks!

This amazing new image resizing technology allows you to resize a picture without decreasing the size of the contents. It does this by adding or removing "unimportant' slices running across a picture (e.g. portions of sky). There is a whitepaper on the technology here http://www.seamcarving.com/. There is an interactive flash demo that has to be seen to be believed! - http://swieskowski.net/carve/. In particular, try the horizontal carving. It looks natural and still has all the proportionally correct primary elements of the picture. More links can be found here: http://drawk.wordpress.com/2007/09/04/as3-content-aware-resizing-or-image-seam-carving/

Dedicated hosting on the Cheap

I am currently with http://www.ixwebhosting.com/ for my simple site www.ddkonline.com - who offer some very good value plans (e.g 300GB of storage, 1500 GB bandwidth for about $10 AUD per month). However, they have been promising to move to ASP.NET 2.0 for months with no result. With a mind to moving to a dedicated server, I happened upon http://tips4hosting.blogspot.com/2007/04/cheapest-dedicated-servers.html with some of the cheapest (unmanaged) dedicated servers around. However, the price increase (approx 15x) for a similar spec is a bit to steep to justify the benefits of a dedicated box. Looks like I'll just have to move providers - or encourage the current provider to move into the 20th century!

Wednesday 5 September 2007

Dynamic SQL WHERE Clauses without sp_executesql

I was asked again today about doing dynamic where clauses without using sp_executesql. See below for my previous answer (19/10/2005). The only thing I would suggest now is to improve my suggestion below is to make the statements even more readable with the ISNULL() statment:

e.g. AND USERNAME >= isnull(@FromUser , uSERNAME). See below for more details.

---ORIGINAL RESPONSE---

I used this a few years ago at Kanes Hire, but I found it quite useful again at Multibase:

If you have a search screen, but want to show ALL records if a parameter is NULL, you can use the following (without IF…THEN conditional logic):

(The trick is that the case statements always resolve to true when the parameter is NULL) – so the criteria is effectively ignored, just like an “ALL”. Seems to perform well on the small queries I used here.

SQL:
AND UserName >= CASE WHEN @FromUser IS NULL THEN UserName ELSE @FromUser END


CREATE PROCEDURE up_StampingReport
@FromDate datetime,
@ToDate datetime,
@FromUser varchar(255),
@ToUser varchar(255),
@IsOutsideSLA varchar(255),
@Rework_Count int,
@TransactionType_ID int,
@IsMatterWithClient char(1),
@IsMatterWithPerpetual char(1)

AS

SELECT *
FROM VW_STAMPREPORT
WHERE
(Expected_datetime >= @FromDate AND Expected_datetime <= @ToDate) AND UserName >= CASE WHEN @FromUser IS NULL THEN UserName ELSE @FromUser END
AND UserName <= CASE WHEN @ToUser IS NULL THEN UserName ELSE @ToUser END AND IsOutsideSLA = CASE WHEN @IsOutsideSLA IS NULL THEN IsOutsideSLA ELSE @IsOutsideSLA END AND Rework_Count = CASE WHEN @Rework_Count IS NULL THEN Rework_Count ELSE @Rework_Count END AND TransactionType_Id = CASE WHEN @TransactionType_ID IS NULL THEN TransactionType_Id ELSE @TransactionType_ID END AND IsMatterWithClient = CASE WHEN @IsMatterWithClient IS NULL THEN IsMatterWithClient ELSE @IsMatterWithClient END AND IsMatterWithPerpetual = CASE WHEN @IsMatterWithPerpetual IS NULL THEN IsMatterWithPerpetual ELSE @IsMatterWithPerpetual END GO