Introduction

This project is all about bringing a 2-D matrix builder to Microsoft SQL Server 2005 (or later). So why do we need this? Consider the following schema:



Now, what we would like to get as a query result is the following :



Notice that the headers are not columns names but the records available in the Table_B table. So how do you get this natively with Microsoft SQL Server? Well, you can't! Unless you use the SQL Server 2-D Matrix Builder.

This project is all about using the hosted .NET Common Langage Runtime in Microsoft SQL Server. All you have to do is uploading the TheMatrixSqlClr.dll assembly into your SQL Server instance and use it. The next two chapters describe how to install this assembly into your SQL Server instance and how to reuse TheMatrix stored procedure.

This project was built by Pascal Belaud, Developer Evangelist at Microsoft France French Blog (http://blogs.msdn.com/Pascal)). Pascal is also the author of a .NET code generator based on SQL Server 2005 called OlyMars (http://www.olymars.net).

Deployment

-- First you need to enable the use of .NET in your SLQ Server instance
exec sp_configure 'CLR Enabled', 1
reconfigure
GO

-- Since this assembly needs external access, your database needs to be TrustWorthy
Alter Database YourDatabase
Set Trustworthy On
GO

Use YourDatabase
GO

-- You need to deploy the assembly in your SQL Server instance
CREATE ASSEMBLY [TheSQLServerMatrix]
FROM '<Your path to the assembly here>\TheMatrixSqlClr.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS
GO

-- You need to declare a new User Defined Type
CREATE TYPE [QueryBuilder]
EXTERNAL NAME [TheSQLServerMatrix].[TheMatrixSqlClr.QueryBuilder]
GO

-- You need to declare a new stored procedure
CREATE PROCEDURE [TheMatrix]
  @rowsQuery [QueryBuilder]
, @rowsQuery_ValueMember nvarchar(max)
, @rowsQuery_DisplayMember nvarchar(max)
, @firstColumnHeader nvarchar(max)
, @columnsQuery [QueryBuilder]
, @columnsQuery_ValueMember nvarchar(max)
, @columnsQuery_DisplayMember nvarchar(max)
, @contentQuery [QueryBuilder]
, @contentQuery_ColumnValueMember nvarchar(max)
, @contentQuery_RowValueMember nvarchar(max)
, @contentQuery_DisplayName nvarchar(max)
, @contentQuery_MissingValue sql_variant
, @diagnose bit = 0

AS
EXTERNAL NAME [TheSQLServerMatrix].[TheMatrixSqlClr.StoredProcedures].[TheMatrix]

Usage

Now that the assembly is declared in our instance, we can use TheMatrix stored procedure like this:

-- Let's declare the first column rows
DECLARE @rowsQuery QueryBuilder
DECLARE @rowsQuery_ValueMember nvarchar(max)
DECLARE @rowsQuery_DisplayMember nvarchar(max)
SET @rowsQuery = QueryBuilder::New_QueryText('Select TableA_ID, TableA_Name From dbo.TableA Order by TableA_Name Asc')
SET @rowsQuery_ValueMember = 'TableA_ID'
SET @rowsQuery_DisplayMember = 'TableA_Name'

-- Let's declare the name of the first column header
DECLARE @firstColumnHeader nvarchar(max)
SET @firstColumnHeader = 'HEADER'

-- Let's declare the others columns headers
DECLARE @columnsQuery QueryBuilder
DECLARE @columnsQuery_ValueMember nvarchar(max)
DECLARE @columnsQuery_DisplayMember nvarchar(max)
SET @columnsQuery = QueryBuilder::New_QueryText('Select TableB_ID, TableB_Name From dbo.TableB Order By TableB_Name Asc')
SET @columnsQuery_ValueMember = 'TableB_ID'
SET @columnsQuery_DisplayMember = 'TableB_Name'

-- Let's declare how to fill the matrix content
DECLARE @contentQuery QueryBuilder
DECLARE @contentQuery_ColumnValueMember nvarchar(max)
DECLARE @contentQuery_RowValueMember nvarchar(max)
DECLARE @contentQuery_DisplayName nvarchar(max)
DECLARE @contentQuery_NullValue sql_variant
SET @contentQuery = QueryBuilder::New_StoredProcedure('spContent')
SET @contentQuery_ColumnValueMember = 'TableC_TableB_ID'
SET @contentQuery_RowValueMember = 'TableC_TableA_ID'
SET @contentQuery_DisplayName = 'TableC_Name'
SET @contentQuery_NullValue = '[NOT AVAILABLE]'

-- We want to see how the Matrix is going to execute all this
DECLARE @diagnose bit
SET @diagnose = 1

EXECUTE [TheMatrixDatabase].[dbo].[TheMatrix] 
   @rowsQuery
  ,@rowsQuery_ValueMember
  ,@rowsQuery_DisplayMember
  ,@firstColumnHeader

  ,@columnsQuery
  ,@columnsQuery_ValueMember
  ,@columnsQuery_DisplayMember

  ,@contentQuery
  ,@contentQuery_RowValueMember
  ,@contentQuery_ColumnValueMember
  ,@contentQuery_DisplayName
  ,@contentQuery_NullValue
  ,@diagnose

When you execute this, you get the following resulset :

HEADER	TableB_5	TableB_6	TableB_7	TableB_8	TableB_9
TableA_1	TableC_1_5	TableC_1_6	TableC_1_7	TableC_1_8	TableC_1_9
TableA_2	[NOT AVAILABLE]	TableC_2_6	TableC_2_7	TableC_2_8	TableC_2_9
TableA_3	TableC_3_5	[NOT AVAILABLE]	TableC_3_7	TableC_3_8	TableC_3_9
TableA_4	TableC_4_	TableC_4_6	TableC_4_7	TableC_4_8	TableC_4_9

Notice that the Matrix allows you to call literal SQL queries or stored procedures. Those queries can also have input parameters if needed.

In the previous example, the matrix content is filled with the resultset returned by the spContent stored procedure:

Create Procedure [dbo].[spContent] As

Select TableC_TableA_ID, TableC_TableB_ID, TableC_Name From dbo.TableC

Since we asked for diagnostic information, here is the output :

14/02/2008 11:22:08.46875- The SQL Server 2-D Matrix Builder
Starting TheMatrix call

14/02/2008 11:22:08.484375- Starting calling the Rows query
Query: Select TableA_ID, TableA_Name From dbo.TableA Order by TableA_Name Asc
	Is Stored Procedure: False
	Parameters number: 0
14/02/2008 11:22:08.484375- ValueMember index for [TableA_ID] is: 0
14/02/2008 11:22:08.484375- DisplayMember index for [TableA_Name] is: 1
14/02/2008 11:22:08.484375- Ending calling the Rows query

14/02/2008 11:22:08.484375- Starting calling the Columns query
Query: Select TableB_ID, TableB_Name From dbo.TableB Order By TableB_Name Asc
	Is Stored Procedure: False
	Parameters number: 0
14/02/2008 11:22:08.484375- ValueMember index for [TableB_ID] is: 0
14/02/2008 11:22:08.484375- DisplayMember index for [TableB_Name] is: 1
14/02/2008 11:22:08.484375- Ending calling the Columns query

14/02/2008 11:22:08.484375- Starting initializing the Matrix content
14/02/2008 11:22:08.484375- Ending initializing the Matrix content

14/02/2008 11:22:08.484375- Starting calling the Content query
Query: spContent
	Is Stored Procedure: True
	Parameters number: 0
14/02/2008 11:22:08.484375- ColumnValueMember index for [TableC_TableB_ID] is: 1
14/02/2008 11:22:08.484375- RowValueMember index for [TableC_TableA_ID] is: 0
14/02/2008 11:22:08.484375- DisplayMember index for [TableC_Name] is: 2
14/02/2008 11:22:08.484375- Ending calling the Content query

14/02/2008 11:22:08.484375- Starting SqlMetaData description
14/02/2008 11:22:08.484375- SqlMetaData, name='HEADER', sqlDbType='NVarChar', maxLength=8, precision=0, scale=0
14/02/2008 11:22:08.5- SqlMetaData, name='TableB_5', sqlDbType='NVarChar', maxLength=15, precision=0, scale=0
14/02/2008 11:22:08.5- SqlMetaData, name='TableB_6', sqlDbType='NVarChar', maxLength=15, precision=0, scale=0
14/02/2008 11:22:08.5- SqlMetaData, name='TableB_7', sqlDbType='NVarChar', maxLength=15, precision=0, scale=0
14/02/2008 11:22:08.5- SqlMetaData, name='TableB_8', sqlDbType='NVarChar', maxLength=15, precision=0, scale=0
14/02/2008 11:22:08.5- SqlMetaData, name='TableB_9', sqlDbType='NVarChar', maxLength=15, precision=0, scale=0
14/02/2008 11:22:08.5- Ending SqlMetaData description

14/02/2008 11:22:08.5- Starting sending back the resultset

(4 row(s) affected)
14/02/2008 11:22:08.5- Ending sending back the resultset

14/02/2008 11:22:08.5- Ending TheMatrix call...

Documentation

The QueryBuilder Used-Defined Type allows you to specify what query should be executed to fill:

• The first column rows (Query 1)
• The columns headers (Query 2)
• The Matrix content (Query 3)

Once you have declared a variable of this type, you can invoke any of its static methods using the :: operator. If you intend to call a literal SQL query, use the NewQueryText method. If you plan to use a stored procedure instead, use the NewStoredProcedure method.

-- If you are using a literal SQL query
Declare @MyLiteralSQLQueryBuilder QueryBuilder
Set @MyLiteralSQLQueryBuilder::New_QueryText(N'Select MyColumn1, MyColumn2, MyColumn3 From MyTable1 Inner Join... Where MyColumn4 = @MyParameter')

-- If you are using a stored procedure
Declare @MyStoredProcedureQueryBuilder QueryBuilder
Set @MyStoredProcedureQueryBuilder::New_StoredProcedure(N'MyStoredProcedureName')

If your literal SQL query or your stored procedure need some input parameters, you can supply their values by calling the SetParameter method:

Declare @Value [your type here]
Set @MyLiteralSQLQueryBuilder::SetParameter(N'@MyParameter', @Value)

Declare @Value [your type here]
Set @MyStoredProcedureQueryBuilder::SetParameter(N'@MyParameter', @Value)

For the Matrix content query only, we need to infere the type of the columns that are returned in the resultset. Although, this product tries to infere this type, it might not be suitable in all cases. Therefore, you can help infere this type by calling one of the following method:

• SetContentType(typeName)
• SetContentType2(typeName, maxLength)
• SetContentType3(typeName, precision, scale)

typeName must be one of the System.Data.SqlDbType enumeration value:



Finally, once you have correctly declared the 3 QueryBuilder variables you need, you can call TheMatrix stored procedure.

The last thing we need to check for our three queries is the following:

• Query 1 and Query 2 need to return two columns (you can return more than two columns but only two columns will be used):
  • one column must be the "Primary Key"
  • one column must be the "Display Value"

• Query 3 needs to return three columns (you can return more than three columns but only three columns will be used):
  • one column must be the "Row Primary Key"
  • one column must be the "Column Primary Key"
  • one column must be the "Display Value" of the Matrix

We also need to supply two values:

• The "Display Value" for the first column header
• The "Display Value" to use if we have a missing value in the Matrix content returned by Query 3

Those parameters will be supplied directly to TheMatrix stored procedure call.

More resources on SQL Server 2-D Matrix Builder

An french article is now available here: The SQL Server 2-D Matrix Builder (http://msdn2.microsoft.com/fr-fr/sqlserver/cc197167.aspx)
This article will also be translated in English very soon. Stay tuned!