Joining SQL Server Tables on Composite Column Values

I am working with a database server that hearkens from the not-too-distant past, where disk space was extremely costly.  It made sense to combine several fields (columns), to make the file (table) more compact.  The values were split and evaluated by mainframe code, which was less costly, because the logic and database were on the same machine.  These are the fiery brimstone of relational database hell.

Passing ORDER BY to SQL Server Stored Procedures as a Parameter

Just about anyone who has worked with legacy code, especially when modifying the functionality of compiled assemblies, has run into this problem.  You can't modify the majority of the business code, and inheriting and overriding the code is not feasible due to impact on the system as a whole.

In the case that inspired this post, I needed to populate a simple ASP.NET 1.1 data repeater with data in a specific order, with minimal impact to the existing code.  The existing code did not contain DataView or DataAdapter ADO.NET objects.  Although I could have composed a SQL statement and switched the SqlCommand object to text query mode, I knew that is a bad idea, because:

1. Composed text queries are begging for an injection attack, and introduce a massive security hole
2. Stored procedures are much more efficient, because they are pre-compiled

My solution was to create a stored procedure on SQL Server, that performs the sorting for me.  All I had to do was pass the order by column to a varchar input parameter.  The trouble with using a parameter in an ORDER BY statement is that it simply doesn't work. The following script WILL NOT WORK.

SELECT * FROM Products ORDER BY @orderByColumn

The solution is to use a bit of both worlds.  Pass the column name as a parameter to the stored procedure, and then compose it into a varchar parameter.  Finally, execute the parameter containing the composite query.

DECLARE @orderByColumn    varchar(50) -- (THE INPUT PARAMETER)
        @sql              varchar(255)

-- Be sure to include a space after ORDER BY
SET @sql = 'SELECT * FROM Products ORDER BY ' + @orderByColumn

EXEC(@sql)

Receiving data in the order you need saves you the trouble of adding a DataTable, DataView, DataAdapter, and possibly other objects, and allows you to bind the command results directly to a data repeater.

Querying Extended ASCII Characters in SQL Server

Part of a project requires conversion of ADABAS to SQL Server.  ADABAS hearkens from the day when storage space was a precious commodity, so the "Packed" data types were invented.  These compress the values stored in the column, to maximize storage utilization.

When converting packed ADABAS File fields to SQL Server relational table columns, some of the packed data was not unpacked(?) correctly, resulting in some interesting characters appearing in SQL Server.  The entire data content of a field needs not be packed; ADABAS allows you to leave the first N characters unpacked, and then pack the remainder of the field, and other such options.

It was my job to find all records across the entire database (we're talking millions of records per table) that contain ASCII characters that do not appear on a standard, 108 key, US English, QWERTY keyboard.  Constructing a query that iterates through all tables and columns that are varchar data type is easy.  However, the SQL Management Studio query editors don't display extended ASCII characters.

The solution was pretty simple.  Cast a byte value to a character type, to specify the extended character ranges.

SELECT RecordID
FROM MyTable
WHERE ((patindex('%[' + char(0) + '-' + char(31) + ']%', ColumnName COLLATE Latin1_General_BIN2) <> 0)
      OR (patindex('%[' + char(127) + '-' + char(255) + ']%', ColumnName COLLATE Latin1_General_BIN2) <> 0))

This selects records from the table where the number of extended ASCII (key codes 0-31, and 127-255) characters in a specific column is not 0.