Archive for February, 2010

T-SQL Stored Procedure (Optional Parameters)

I use a lot of queries to drive reports and interactive search tools and the most common requirement of all my queries is optional parameters. I’ve used a ton of different methods to pull this off in the past, but I primarily use one approach when I build stored procedures that will be used with optional parameters that is efficient and easy to read.

-- //////////////////////////////////////////
-- Stored Procedure - Optional Params (NEW WAY)
-- //////////////////////////////////////////
	@OrderNumber VARCHAR(10),
	@TrackingNumber VARCHAR(10)
	WHERE (@OrderNumber IS NULL OR @OrderNumber = '' OR ORDERNUMBER = @OrderNumber)
		AND (@TrackingNumber IS NULL OR @TrackingNumber = '' OR TRACKINGNUMBER = @TrackingNumber)

-- //////////////////////////////////////////////////
-- Stored Procedure - Optional Params - Dynamic SQL (OLD WAY)
-- //////////////////////////////////////////////////
	@OrderNumber VARCHAR(10),
	@TrackingNumber VARCHAR(10)

		SET @STRSQL = @STRSQL + ' AND ORDERNUMBER = ''' + @OrderNumber + ''''

		SET @STRSQL = @STRSQL + ' AND TRACKINGNUMBER = ''' + @TrackingNumber + ''''


As you can see, the “New Way” has a lot less code and does not use dynamic T-SQL. In addition to checking for NULL parameter values, I also suggest you check for empty strings since text boxes normally return an empty string.

Now you have the choice of sending NULL or “” and the criteria will be skipped.

Download Your Site to HTML

HTTrack website copier is awesome and FREE! I’ve been using it for a few months and I’ve already saved myself and my team members hundreds of hours by downloading full working copies of various websites locally.

Here are a few examples of websites I download.

  1. A client was having problems with their hosting company who refused to give them access to their source code (developed by a 3rd party) to their CF website. After weeks of trying to solve the problem, they decided to switch hosting companies. During their migration to a hosting company and a new CMS solution, they wanted to keep a copy of the old site online. Regardless of the engine running the website, HTTrack was able to download a working copy of the site to HTML, minus the dynamic features (Search, CMS Admin, etc…) in a few minutes.
  2. We inherited a old web server at working running Plone 2.0.4. The machine was running on old hardware that was failing (2/3 dead HDD, broken RAID 1) and Zope was randomly crashing. After spending 3 days trying to get the machine stable, I decide to convert the site to HTML and put on one of our newer “stable” web servers. The process was a little mess, since our plone instance pointed to folders paths vs. actual files (/index_html vs /index.hmtl) the final static site had a lot of weird and randomly directed links! The final static HTML site probably took ~8 solid hours of HTML clean-up, but there was also a lot of bad/obsolete content in the existing portal that made this process painful!
  3. I have a few websites I like to use as samples/demo that show better as interactive! Using HTTrack and controlling the depth of link following/downloading (like first page only), I can download an offline working copy of a sites main page! This is a great way to demo a feature/capability/competitor website…

A few other good things to know, a couple sites I’ve downloaded require you to login first. This isn’t an issue with HTTrack, since you can build your own cookies to apply to your capture. It was a bit tricky getting the cookie format correct (Used Netscape Cookie Format), but after a few minutes I was able to take the cookie data from IE/FireFox and have a perfect working cookie for HTTrack to use!

Once again, this is FREE utility that is handy to have around!


SubSonic 2.2 – RESTHandler (Add ProviderName Support)

I’ve been using SubSonic’s built-in RESTHandler for some basic AJAX features in my portal at work. This is a really nice extra feature, and provides a quick way to pull xml/json/rss from your DAL. I noticed that a few features based on this handler had stopped working a few weeks ago. I found that SubSonic’s RESTHandler had no way to pass in the provider name as part of the url. I currently have 5 different providers (databases) setup for SubSonic, so I needed a way to point to the various providers when calling a URL.

Edit the following file “SubSonic/HttpHandlers/RESTfullUrl.cs”

1. Add new property to access “ProviderName”

private string _providerName = String.Empty;
public string ProviderName
get { return _providerName; }
set { _providerName = value; }

2. Modify ParseURL()

private void ParseUrl()
//work backwards up the URL
//first thing, check for a query string and strip it
string workingUrl = _rawUrl;

//strip off the query string - it'll be used later
workingUrl = Strings.Chop(workingUrl, "?");


string[] bits = workingUrl.Split(new[] {'/'}, StringSplitOptions.RemoveEmptyEntries);
//the URL should be split out into
//protocol ("http");
//domain ("");
//service directory ("services");
//provider name ("northwinds")
//table or sp ("products")
//command.format ("list.xml")

//the index of each item within the URL
int commandIndex = bits.Length - 1;
int tableSPIndex = bits.Length - 2;
int providerIndex = bits.Length - 3;

//now check to see if the last item on the URL is a key, or
//the last item is the command - list/show/etc
//set it
//the last item is a key, set it, and then the command
_key = Path.GetFileNameWithoutExtension(bits[bits.Length - 1]);

//the command is the next item up the chain
_restCommand = DecideCommand(Path.GetFileNameWithoutExtension(bits[commandIndex]));

//evaluate the command
if(_restCommand == RESTCommand.Exec)
_spName = bits[tableSPIndex];
_tableName = bits[tableSPIndex];

_providerName = bits[providerIndex];


Edit the following file “SubSonic/HttpHandlers/RESTHandler.cs”

3. Update GenerateReturnSet(), line #286.

q = new Query(_url.TableName);  //before
q = new Query(_url.TableName,_url.ProviderName);  //after

Here is an example using the new syntax that supports provider name.