tim laqua dot com Thoughts and Code from Tim Laqua

23May/090

Parsing QueryString (GET) Variables From a URL using T-SQL

First of all - I know, don't do this. The application that put the URL in the column in the first place is MUCH better at handling URLs and ideally, you would just add columns for the GET variables you're after and have the application put those in. Parsing them out after the fact from a VARCHAR field is insane.

So now we're here and we need to do that thing that we said we shouldn't do. My approach is to use a Table Valued Function that only returns one column - the value of the variable or NULL if it can't find the variable in the querystring.

23May/090

A Cleaner Way to Detect the State of a SQL Server Agent Job In SQL Server 2005

I have always felt that the traditional approach to getting Agent Job status by creating a table and populating it with output from xp_sqlagent_enum_jobs was incredibly crude. So I poked around a bit and came up with a solution using a Table Valued Function that queries sysjobactivity to determine the status of a given job. The reason I use a Table Valued Function is because we have a few tables that contain a list of jobs that need to be started at certain points in a given process (and they may or may not already be running) - with the TVF, you can simply CROSS APPLY the function and bob's your uncle.