iHwy Development Blog

I had a case recently where I had to query some data from a Microsoft SQL Server 2005 database and insert a row in the results before the result set. Here's a spiffy little example of how I did it. Neat little trick that can come in handy for cases like this.

-- First you create a var that's a table with cols that match you desired resultset:

declare @tbl table (
  categoryID int, categoryName nvarchar(100)
)

-- Then insert the data you need in the first row:

insert into @tbl (categoryID, categoryName)
 values (-1, 'Unknown')

-- Then select and insert the rest of the data:

insert into @tbl (categoryID, categoryName)
select categoryID, categoryName
from category
order by categoryName

-- Check the results:

select * from @tbl

This was very handy recently inside of a Cold Fusion cfquery tag for adding a "dummy" category to a list of categories. The dummy category got the "Unknown" category name and then the rest of the categories were added to the table. Then in some other code some sets of products got related back to the categories in the table. Products that didn't have a categoryID assigned to them fell into the "Unknown" category for display in an heirarchical tree we did with some jQuery.

This is a neat little trick I picked up. If you've ever needed to create a delimited list from a single column of data stored in SQL Server and felt like it required unnecessary arm wrestling due to SQL syntax limitations, this could be handy for you next time, too:

Let's say you have a column called "lastName" and want to get a comma-delimited list of last names. Here's a way:

-- set up a var for the list

declare @list varchar(1000)
set @list = ''

-- create the delimited list using a query

select @list = @list + lastName + ', ' from person

-- trim off the trailing comma and space if necessary

if @list <> '' set @list = left(@list, len(@list) - 1)

-- take a look at the results

print @list

Here are some example results:

Rubble, Flintstone, Jetson, Munster

Neat trick.