Empty Thoughts

Every once in a while you need a query to return results in a crosstab format. In the past I’ve always approached this in the typical way of using CASE statements to check the key and then outputing the value or returning zero / an empty space. This works well but it can get verbose at times.

Today I ran across a posting on the Rozenshtein Method. It’s a really amazing approach and it’s SQL 92 compliant. The post can be found here:

http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8

I will not describe it since that’s already done, but I highly recommend you check it out.

Although the example uses the case of crosstabing based on integers, this same method can be used effectively with characters, since every character has an ANSI equivalent. At a client site we used this method to build a crosstab of racial ethnic diversity counts. The end result is kind of a mess, but it works great. For your amusement, here’s a snippet:

SELECT l.id, SUM(l.TotalCount) AS total_count, SUM(l.FemaleCount) AS FemaleCount,
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-801)))) AS "801",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-802)))) AS "802",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-803)))) AS "803",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-804)))) AS "804",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-805)))) AS "805",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-806)))) AS "806",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-807)))) AS "807",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-808)))) AS "808",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-809)))) AS "809",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-810)))) AS "810",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-811)))) AS "811" 
FROM RacialEthnicProfilingOnAirlines l
INNER JOIN RacialEthnicCounts e
    ON l.id = e.id
INNER JOIN Airlines a 
    ON l.id = a.id
WHERE a.id IN (SELECT id FROM [Airlines] WHERE [Name] = 'UNTIDY')
GROUP BY l.id
ORDER BY l.id

Today I used a little tidbit that I thought I would pass along. Note that although I’m specifically using SQL Server in these examples the ideas should work regardless. Most often I see two different approaches to using dynamic WHERE clauses in stored procedures:

1. Separate Queries – The first method I see a lot is just setting up separate queries, and you end up seeing code that looks something like this:

IF @p_CompanyID IS NOT NULL AND @p_Name IS NOT NULL BEGIN
  SELECT * FROM Company WHERE CompanyID = @p_CompanyID AND Name = @p_Name
END ELSE BEGIN
IF @p_CompanyID IS NOT NULL BEGIN
  SELECT * FROM Company WHERE CompanyID = @p_CompanyID
END ELSE BEGIN
IF @p_Name IS NOT NULL BEGIN
  SELECT * FROM Company WHERE Name = @p_Name
END ELSE BEGIN
  SELECT * FROM Company
END
END
END

This gets ugly really quick.

2. Dynamic SQL – In this approach a query is built up from the parameters and an sp_executesql is used to dynamically execute the query. This can be a very bad approach, especially if the parameters being passed in are generated from some form of user search form input. This is the classic SQL Injection problem.

DECLARE @p_SQL AS NVARCHAR(3500)
SET @p_SQL = 'SELECT * FROM [Company] WHERE 1 = 1'
IF @p_CompanyID IS NOT NULL BEGIN
  SET @p_SQL = @p_SQL + ' AND [CompanyID] = ' + @p_CompanyID
END
IF @p_Name IS NOT NULL BEGIN
  SET @p_SQL = @p_SQL + ' AND [Name] = ''' + @p_Name + ''
END

exec sp_executesql @p_SQL

This is a lot cleaner than number 1, but it has problems. Also notice the use of the WHERE 1 = 1 statement. That’s a nice little trick to keep you from having to check if you need the AND statement or not.

3. And the Winner Is – Finally, here’s a much better approach, is clean and doesn’t have the sql injection problems:

SELECT * FROM Company
WHERE 
  ((@p_CompanyID IS NULL) OR [CompanyID] = @p_CompanyID)
  AND ((@p_Name IS NULL) OR [Name] = @p_Name)
  AND ((@p_City IS NULL) OR [City] = @p_City)
  AND ((@p_State IS NULL) OR [State] = @p_State)
  AND ((@p_Zip IS NULL) OR [Zip] = @p_Zip)

It takes a minute to get your head around what’s going on, but basically we’re saying give me this row if the parameter is not filtered out or the parameter equals the record we’re looking for. You can build this up as I have done here, and keeps things clean. You can see how a simple search form could be implemented as shown above.