Today, I came across a problem where, after a lot of searching through forums and other various mediums of programming support, I was basically told it couldn't be done. I don't like to take "it's impossible" for an answer, so I continued to dive into it and I finally came up with a solution.

The Problem

Using an SQL database, I had a value that I was searching for. I knew that the row would not be there in the case that we were using the default value. I needed to always return something, even if it was just the default value. I also needed to return multiple default values from multiple rows as a single row. One catch that was the results needed to be returned through a view. The reason for this is that we were using some home-brewed replication of our database, that only synced the necessary data into tables. When information that was normally retrieved through a view was needed, we simply created a table with the same structure and name as the view. Made things simple, right? Great for the replication, didn't seem so in this case.

The Solution

At first, I thought, I could just use the ISNULL command.

1
2
3
4
5
6
SELECT
	ISNULL([Field], 'DefaultValue') AS [FieldName]
FROM
	[Table]
WHERE
	[KeyField] = 'KeyValue'

The problem with this was that since it didn't return any rows, there was nothing to use ISNULL on. To remedy, that I decided to go the route of a subquery.

1
2
3
4
5
6
7
8
SELECT
	ISNULL((
		SELECT
			[Table].[Field]
		FROM
			[Table]
		WHERE
			[Table].[KeyField] = 'KeyValue1'),'DefaultValue1') AS [Field1]

And with that I was able to get the results I needed, however, I needed to return the values of multiple rows as a single row.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
	[Field1]
	,[Field2]
FROM
	(
		SELECT
			ISNULL((
				SELECT
					[Table].[Field]
				FROM
					[Table]
				WHERE
					[Table].[KeyField] = 'KeyValue1'),'DefaultValue1') AS [Field1]
			,ISNULL((
				SELECT
					[Table].[Field]
				FROM
					[Table]
				WHERE
					[Table].[KeyField] = 'KeyValue2'),'DefaultValue2') AS [Field2]
	) AS Results

As a bit of warning:  My solution does use a number of subqueries and depending on your database structure and row count this solution could be a bit on the sluggish side.  As always, make sure you run through the execution plan to optimize the query where you can.