Programming a CLR Stored Procedure - SQL Server 2008

Now that the basic overview of what’s available is complete, it’s time to get into some code! The example used in this chapter will be a dynamic cross-tabulation of some sales data in the AdventureWorks sample database that’s included with SQL Server 2005 and 2008. Given the data in the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables, the goal will be to produce a report based on a user-specified date range, in which the columns are sales months and each row aggregates total sales within each month, by territory.

Before starting work on any CLR routine, the developer should ask, “Why should this routine be programmed using the CLR?” Remember that in most cases, T-SQL is still the preferred method of SQL Server programming, so give this question serious thought before continuing. In this case, the argument in favor of using a CLR routine is fairly obvious. Although this problem can be solved using only T-SQL, it’s a messy prospect at best. In order to accomplish this task, the routine first must determine in which months sales occurred within the input date range. Then, using that set of months, a query must be devised to create a column for each month and aggregate the appropriate data by territory.

This task is made slightly easier than it was in previous versions of SQL Server, thanks to the inclusionof the PIVOT operator. This operator allows T-SQL developers to more easily write queries that transform rows into columns, a common reporting technique known as either pivoting or cross-tabulating. However, PIVOT doesn’t provide dynamic capabilities, so the developer still needs to perform fairly complex string concatenation to get things working. Concatenating strings is tricky and inefficient in T-SQL. Using the .NET Framework’s StringBuilder class is a much nicer prospect. Avoiding complex T-SQL string manipulation is argument enough to do this job within a CLR routine.

Once the determination to use a CLR routine has been made, the developer next must ask, “What is the appropriate type of routine to use for this job?” Generally speaking, this will be a fairly straightforward question; for instance, a CLR user-defined type and a CLR user-defined trigger obviously serve quite different purposes. However, the specific problem for this situation isn’t so straightforward. There are two obvious choices: a CLR table-valued function and a CLR stored procedure.

The requirement for this task is to return a result set to the client containing the cross-tabulated data. Both CLR table-valued functions and CLR stored procedures can return result sets to the client. However, as will be discussed in the next chapter, CLR table-valued functions must have their output columns predefined. In this case, the column list is dynamic; if the user enters a 3-month date range, up to four columns will appear in the result set—one for each month in which there were sales, and one for the territory sales are being aggregated for. Likewise, if the user enters a 1-year date range, up to 13 columns may be returned. Therefore, it isn’t possible to predefine the column list, and the only choice is to use a CLR stored procedure.

Starting aVisual Studio 2008 SQL Server Project

Once you have decided to program a CLR routine, the first step is to start Visual Studio 2008 and create a new project. Figure shows the menu option to pick to launch the New Project Wizard.

Opening a new project in Visual Studio

Opening a new project in Visual Studio

Visual Studio includes a project template for SQL Server projects, which automatically creates all of the necessary references and can create appropriate empty classes for all of the SQL Server CLR routine types. Although you could use a Class Library template instead and do all of this manually, that’s not an especially efficient use of time. So we definitely recommend that you use the SQL Server Project template when developing CLR routines.

Figure shows the SQL Server Project template being chosen from the available database project templates. On this system, only C# has been installed; on a system with VB .NET, the same option would appear under that language’s option tree.

Selecting the SQL Server Project template

Selecting the SQL Server Project template

This project has been named SalesCrossTabs, since it’s going to contain at least one cross tabulation of sales data (perhaps more will be added in the future). A single SQL Server project can contain any number of CLR routines. However, it’s recommended that any one project logically group only a small number of routines. If a single change to a single routine is made, you should not need to reapply every assembly referenced by the database.

After clicking the OK button, you are presented with the New Database Reference dialog box, as shown in Figure. By specifying a connection at this point, the project is easier to deploy and test from within the Visual Studio environment. For this example, we are making a connection to the AdventureWorks database on our local server.

Create a new database reference if the correct one doesn’t already exist.

Create a new database reference if the correct one doesn’t already exist.

At this point, a new, blank project has been created and is ready to have some code added. Right-click the project name in Solution Explorer, click Add, and then click Stored Procedure, as shown in Figure.

Adding a stored procedure to the project

Adding a stored procedure to the project

The final step in adding the new stored procedure is to name it. Figure shows the window that will appear after clicking Stored Procedure. The Stored Procedure template is selected, and the procedure has been named Get Sales Per Territory ByMonth. Developers should remember that, just as in naming T-SQL stored procedures, descriptive, self-documenting names go a long way toward making development and maintenance easier.

Naming the stored procedure

Naming the stored procedure

Anatomy of a Stored Procedure

After the new stored procedure has been added the project, the following code will be appear in the editing window:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetSalesPerTerritoryByMonth()
{
// Put your code here
}
};

Notice that the Microsoft.SqlServer.Server and System.Data.SqlTypes namespaces have been automatically included in this project. Both of these namespaces have very specific purposes within a routine and will be necessary within most SQL Server CLR projects.

The Microsoft.SqlServer.Server namespace is necessary, as previously mentioned, for the attributes that must decorate all routines to be hosted within SQL Server. In this case, the GetSalesPerTerritoryByMonth method has been decorated with the SqlProcedure attribute. This indicates that the method is a stored procedure. The method has also been defined as static. Since this method will be called without an object instantiation, it would not be available if not defined as static. The Microsoft.SqlServer.Server namespace is also included in order to provide access to the calling context, for data access and returning data.

The System.Data.SqlTypes namespace provides datatypes that correspond to each of the SQL Server datatypes. For instance, the equivalent of SQL Server’s INTEGER datatype isn’t .NET’s System.Int32 datatype. Instead, it’s SqlTypes .SqlInt32 . Although these types can be cast between each other freely, not all types have direct equivalents. Many of the SQL Server types have slightly different implementations than what would seem to be their .NET siblings. For that reason, and to provide some insulation in case of future underlying structural changes, it’s important to use these types instead of the native .NET types when dealing with data returned from SQL Server, including both parameters to the routine and data read using a SqlDataReader or DataSet object.

Aside from the included namespaces, note that the return type of the GetSalesPerTerritoryByMonth method is void. SQL Server stored procedures can return either 32-bit integers or nothing at all. In this case, the stored procedure won’t have a return value. That’s generally a good idea, because SQL Server will override the return value should an error occur within the stored procedure; so output parameters are considered to be a better option for returning scalar values to a client. However, should a developer want to implement a return value from this stored procedure, the allowed datatypes are SqlInt32 and SqlInt16.

Adding Parameters

Most stored procedures will have one or more parameters to allow users to pass in arguments that can tell the stored procedure which data to return. In the case of this particular stored procedure, two parameters will be added to facilitate getting data using a date range (one of the requirements outlined in the section “Programming a CLR Stored Procedure”). These parameters will be called StartDate and EndDate, and each will be defined as type SqlDateTime.

These two parameters are added to the method definition, just like parameters to any C# method:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetSalesPerTerritoryByMonth( SqlDateTime StartDate,
SqlDateTime EndDate)
{
// Put your code here
}

In this case, these parameters are required input parameters. Output parameters can be defined by using the C# ref (reference) keyword before the datatype. This will then allow developers to use SQL Server’s OUTPUT keyword in order to get back scalar values from the stored procedure.

Unfortunately, neither optional parameters nor default parameter values are currently supported by CLR stored procedures.

Defining the Problem

At this point, the stored procedure is syntactically complete and could be deployed as is; but of course, it wouldn’t do anything! It’s time to code the meat of the procedure. But first, it’s good to take a step back and figure out what it should do.

The final goal, as previously mentioned, is to cross-tabulate sales totals per territory, with a column for each month in which sales took place. This goal can be accomplished using the following steps:

  1. Select a list of the months and years in which sales took place, from the Sales.SalesOrderHeader table.
  2. Using the list of months, construct a query using the PIVOT operator that returns the desired cross-tabulation.
  3. Return the cross-tabulated result set to the client.

The Sales.SalesOrderHeader table contains one row for each sale, and includes a column called OrderDate—the date the sale was made. For the sake of this stored procedure, a distinct list of the months and years in which sales were made will be considered. The following query returns that data:

SELECT DISTINCT
DATEPART(yyyy, OrderDate) AS YearPart,
DATEPART(mm, OrderDate) AS MonthPart
FROM Sales.SalesOrderHeader
ORDER BY YearPart, MonthPart

Once the stored procedure has that data, it needs to create the actual cross-tab query. This query needs to use the dates from Sales.SalesOrderHeader. For each month, it should calculate the sum of the amounts in the LineTotal column of the Sales.SalesOrderDetail table. And of course, this data should be aggregated per territory. The TerritoryId column of the Sales.SalesOrderHeader table will be used for that purpose.

The first step in creating the cross-tab query is to pull the actual data required. The following query returns the territory ID, order date formatted as YYYY-MM, and total line item amount for each line item in the SalesOrderDetail table:

SELECT
TerritoryId,
CONVERT(char(7), h.OrderDate, 120) AS theDate,
d.LineTotal
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID

Figure shows a few of the 121,317 rows of data returned by this query.

Unaggregated sales data

Unaggregated sales data

Using the PIVOT operator, this query can be turned into a cross-tab. For instance, to report on sales from June and July of 2004, the following query could be used:

SELECT
TerritoryId,
[2004-06],
[2004-07]
FROM
(
SELECT
TerritoryId,
CONVERT(char(7), h.OrderDate, 120) AS YYYY_MM,
d.LineTotal
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
) p
PIVOT
(
SUM (LineTotal)
FOR YYYY_MM IN
(
[2004-06],
[2004-07]
)
) AS pvt
ORDER BY TerritoryId

Figure shows the results of this query. The data has now been aggregated and crosstabulated. Note that a couple of the values are null, which indicates a territory that did not have sales for that month.

Cross-tabulated sales data

Cross-tabulated sales data

In the actual stored procedure, the tokens representing June and July 2004 will be replaced by tokens for the actual months from the input date range, as determined by the StartDate and EndDate parameters and the first query. Then the full cross-tab query will be concatenated. All that’s left from the three steps defined previously is to return the results to the caller. You have a couple of choices for how to tackle that challenge.

Using the SqlPipe

As mentioned in previous sections, SqlContext is an object available from within the scope of CLR routines. This object is defined in the Microsoft.SqlServer.Server namespace as a sealed class with a private constructor, so you don’t create an instance of it; rather, you just use it. The following code, for instance, is invalid:

//This code does not work -- the constructor for SqlContext is private
SqlContext context = new SqlContext();

Instead, just use the object as is. To use the SqlPipe, which is the object we need for this exercise, the following code might be used:

//Get a reference to the SqlPipe for this calling context
SqlPipe pipe = SqlContext.Pipe;

So what is the SqlPipe object? This object allows the developer to send data or commands to be executed from a CLR routine back to the caller.

The Send() Method

The Send() method, which as you can guess is used to actually send the data, has three overloads:

  • Send(string message) sends a string, which will be interpreted as a message. Think InfoMessage in ADO.NET or the messages pane in SQL Server Management Studio. Sending strings using Send() has the same effect as using the T-SQL PRINT statement.
  • Send(SqlDataRecord record) sends a single record back to the caller. This is used in conjunction with the SendResultsStart() and SendResultsEnd() methods to manually send a table a row at a time. Getting it working can be quite a hassle, and it’s really not recommended for most applications. See the section “Table-Valued User-Defined Functions” in the next chapter for a much nicer approach.
  • Send(SqlDataReader reader) sends an entire table back to the caller, in one shot. This is much nicer than doing things row by row, but also just as difficult to set up for sending back data that isn’t already in a SqlDataReader object. Luckily, this particular stored procedure doesn’t have that problem. It uses a SqlDataReader, so this method can be used to directly stream back the data read from the SQL Server.

The Send() methods can be called any number of times during the course of a stored procedure. Just like native T-SQL stored procedures, CLR procedures can return multiple result sets and multiple messages or errors. But by far the most common overload used will be the one that accepts SqlDataReader. The following code fragment shows a good example of the utility of this method:

command.CommandText = "SELECT * FROM Sales.SalesOrderHeader";
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);

In this fragment, it’s assumed that the connection and command objects have already been instantiated and the connection has been opened. A reader is populated with the SQL, which selects all columns and all rows from the Sales.SalesOrderHeader table. The SqlDataReader can be passed to the Send() method as is, and the caller will receive the data as a result set.

Although this example is quite simplistic, it illustrates the ease with which the Send() method can be used to return data back to the caller when the data is already in a SqlDataReader object.

The ExecuteAndSend() Method

The problem with sending a SqlDataReader back to the caller is that all of the data will be marshaled through the CLR process space on its way back. Since, in this case, the caller generated the data (it came from a table in SQL Server), it would be nice to be able to make the caller return the data on its own—without having to send the data back and forth.

This is where the ExecuteAndSend() method comes into play. This method accepts a SqlCommand object, which should have both CommandText and Parameters values (if necessary) defined. This tells the calling context to execute the command and process the output itself.

Letting the caller do the work without sending the data back is quite a bit faster. In some cases, performance can improve by up to 50 percent. Sending all of that data between processes is a lot of work. But this performance improvement comes at a cost; one of the benefits of handling the data within the CLR routine is control. Take the following code fragment, for example:

command.CommandText = "SELECT * FROM Sales.ERRORSalesOrderHeader";
try
{
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);
}
catch (Exception e)
{
//Do something smart here
}

This fragment is similar to the fragment discussed in the previous Send() method example. It requests all of the rows and columns from the table, and then sends the data back to the caller using the Send() method. This work is wrapped in a try/catch block. The developer, perhaps, can do something to handle any exceptions that occur. And indeed, in this code block, an exception will occur. The table Sales.ERRORSalesOrderHeader doesn’t exist in the AdventureWorks database.

This exception will occur in the CLR routine—the ExecuteReader() method will fail. At that point, the exception will be caught by the catch block. But what about the following code fragment, which uses the ExecuteAndSend() method:

command.CommandText = "SELECT * FROM Sales.ERRORSalesOrderHeader";
try
{
SqlContext.Pipe.ExecuteAndSend(command)
}
catch (Exception e)
{
//Do something smart here
}

Remember that the ExecuteAndSend() method tells the caller to handle all output from whatever T-SQL is sent down the pipe. This includes exceptions. So in this case, the catch block is hit, but by then it’s already too late. The caller has already received the exception, and catching it in the CLR routine isn’t especially useful.

So which method, Send() or ExecuteAndSend(), is appropriate for the sales cross-tabulation stored procedure? Given the simplicity of the example, the ExecuteAndSend() method makes more sense. It has greater performance than Send(), which is always a benefit. And there’s really nothing that can be done if an exception is encountered in the final T-SQL to generate the result set.

Putting It All Together: Coding the Body of the Stored Procedure

Now that the techniques have been defined, putting together the complete stored procedure is a relatively straightforward process.

Recall that the first step is to get the list of months and years in which sales took place, within the input date range. Given that the pivot query will use date tokens formatted as YYYY-MM, it will be easier to process the unique tokens in the CLR stored procedure if they’re queried from the database in that format. So the query used will be slightly different from the one shown in the “Defining the Problem” section. The following code fragment will be used to get the months and years into a SqlDataReader object:

//Get a SqlCommand object
SqlCommand command = new SqlCommand();
//Use the context connection
command.Connection = new SqlConnection("Context connection=true");
command.Connection.Open();
//Define the T-SQL to execute
string sql =
"SELECT DISTINCT " +
"CONVERT(char(7), h.OrderDate, 120) AS YYYY_MM " +
"FROM Sales.SalesOrderHeader h " +
"WHERE h.OrderDate BETWEEN @StartDate AND @EndDate " +
"ORDER BY YYYY_MM";
command.CommandText = sql.ToString();
//Assign the StartDate and EndDate parameters
SqlParameter param =
command.Parameters.Add("@StartDate", SqlDbType.DateTime);
param.Value = StartDate;
param = command.Parameters.Add("@EndDate", SqlDbType.DateTime);
param.Value = EndDate;
//Get the data
SqlDataReader reader = command.ExecuteReader();

This code uses the same SqlCommand and SqlDataReader syntax as it would if this were being used for an ADO.NET client. Keep in mind that this code won’t work unless the System.Data.SqlClient namespace is included with a using directive. The only difference between this example and a client application is the connection string, which tells SQL Server that this should connect back to the caller’s context instead of a remote server. Everything else is the same—the connection is even opened, as if this were a client instead of running within SQL Server’s process space.

As a result of this code, the reader object will contain one row for each month in which sales took place within the input date range (that is, the range between the values of the StartDate and EndDate parameters). Looking back at the fully formed pivot query, you can see that the tokens for each month need to go into two identical comma-delimited lists: one in the outer SELECT list and one in the FOR clause. Since these are identical lists, they only need to be built once. The following code handles that:

//Get a StringBuilder object
System.Text.StringBuilder yearsMonths = new System.Text.StringBuilder();
//Loop through each row in the reader, adding the value to the StringBuilder
while (reader.Read())
{
yearsMonths.Append("[" + (string)reader["YYYY_MM"] + "], ");
}
//Close the reader
reader.Close();
//Remove the final comma in the list
yearsMonths.Remove(yearsMonths.Length - 2, 1);

A StringBuilder is used in this code instead of a System.string. This makes building the list a bit more efficient. For each row, the value of the YYYY_MM column (the only column in the reader) is enclosed in square brackets, as required by the PIVOT operator. Then a comma and a space are appended to the end of the token. The extra comma after the final token is removed after the loop is done. Finally, SqlDataReader is closed. When working with SqlDataReader, it’s a good idea to close it as soon as data retrieval is finished in order to disconnect from the database and save resources.

Now that the comma-delimited list is built, all that’s left is to build the cross-tab query and send it back to the caller using the ExecuteAndSend() method. The following code shows how that’s done:

//Define the cross-tab query
sql =
"SELECT TerritoryId, " +
yearsMonths.ToString() +
"FROM " +
"(" +
"SELECT " +
"TerritoryId, " +
"CONVERT(char(7), h.OrderDate, 120) AS YYYY_MM, " +
"d.LineTotal " +
"FROM Sales.SalesOrderHeader h " +
"JOIN Sales.SalesOrderDetail d " +
"ON h.SalesOrderID = d.SalesOrderID " +
"WHERE h.OrderDate BETWEEN @StartDate AND @EndDate " +
") p " +
"PIVOT " +
"( " +
"SUM (LineTotal) " +
"FOR YYYY_MM IN " +
"( " +
yearsMonths.ToString() +
") " +
") AS pvt " +
"ORDER BY TerritoryId";
//Set the CommandText
command.CommandText = sql.ToString();
//Have the caller execute the cross-tab query
SqlContext.Pipe.ExecuteAndSend(command);
//Close the connection
command.Connection.Close();

Note that we are using the same command object as we did for building the comma-delimited list of months in which sales took place. This command object already has the StartDate and EndDate parameters set; since the cross-tab query uses the same parameters, the parameters collection doesn’t need to be repopulated. Just like when programming in an ADO.NET client, the connection should be closed when the process is finished with it.

At this point, the CLR stored procedure is completely functional as per the three design goals, so it’s ready for a test drive.

Testing the Stored Procedure

Visual Studio makes deploying the stored procedure to the test SQL Server quite easy. Just rightclick the project name (in this case, SalesCrossTabs) and click Deploy, as shown in Figure.

Deploying the assembly to the test server

Deploying the assembly to the test server

Once the procedure is deployed, testing it is simple. Log in to SQL Server Management Studio and execute the following batch of T-SQL:

USE AdventureWorks
GO
EXEC GetSalesPerTerritoryByMonth
@StartDate = '20040501',
@EndDate = '20040701'
GO

If everything is properly compiled and deployed, this should output a result set containing cross-tabulated data for the period between May 1, 2004, and July 1, 2004. Figure shows the correct output.

Cross-tabulated sales data for the period between May 1, 2004, and July 1, 2004

Cross-tabulated sales data for the period between May 1, 2004, and July 1, 2004

Note that running the stored procedure might result in the following message:

Execution of user code in the .NET Framework is disabled. Use sp_configure "clr enabled" to enable execution of user code in the .NET Framework.

If this happens, execute the following batch of T-SQL to turn on CLR integration for the SQL Server:

USE AdventureWorks
GO
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
GO

Using the sp_configure system stored procedure to enable CLR integration is required before CLR routines can be run in any database. Keep in mind that enabling or disabling CLR integration is a serverwide setting.

Once the stored procedure is running properly, it will appear that the stored procedure works as designed! However, perhaps some deeper testing is warranted to ensure that the procedure really is as robust as it should be. Figure shows the output from the following batch of T-SQL:

USE AdventureWorks
GO
EXEC GetSalesPerTerritoryByMonth
@StartDate = '20050501',
@EndDate = '20050701'
GO

Attempting to cross-tabulate sales data for the period between May 1, 2005, and July 1, 2005

Attempting to cross-tabulate sales data for the period between May 1, 2005, and July 1, 2005

Debugging the Procedure

What a difference a year makes! Luckily, since this stored procedure is being coded in Visual Studio, the integrated debugging environment can be used to track down the problem. In Solution Explorer, expand the Test Scripts node and double-click Test.sql. This will open a template that can contain T-SQL code to invoke CLR routines for debugging. Paste the following T-SQL into the Stored Procedure section:

EXEC GetSalesPerTerritoryByMonth
@StartDate = '20050501',
@EndDate = '20050701'

Now return to the code for the managed stored procedure and put the cursor on the first line:

SqlCommand command = new SqlCommand();

Pressing the F9 key will toggle a breakpoint for that line.

Before starting the debug session, open Server Explorer by selecting View ➤Server Explorer, as shown in Figure. In Server Explorer, right-click the database connection being used for this project and make sure that both Application Debugging and Allow SQL/CLR Debugging are checked, as shown in Figure.

Opening Server Explorer in Visual Studio

Opening Server Explorer in Visual Studio

Allowing SQL/CLR debugging for the project’s database connection

Allowing SQL/CLR debugging for the project’s database connection

Once debugging is enabled, press the F5 key, and Visual Studio will enter debug mode. If all is working as it should, the breakpoint should be hit, and code execution will stop on the first line of the stored procedure.

Use the F10 key to step through the stored procedure one line at a time, using the Locals pane to check the value of all of the variables. Stop stepping through on this line: yearsMonths.Remove(yearsMonths.Length - 2, 1);

Look at the value of the yearsMonths variable in the Locals pane—it’s empty. No characters can be removed from an empty string!

As it turns out, this stored procedure wasn’t coded properly to be able to handle date ranges in which there is no data. This is definitely a big problem, since the output requires a column per each month in the input date range that sales occurred. Without any data, there can be no columns in the output. The stored procedure needs to return an error if no data is present.

Throwing Exceptions in CLR Routines

Any exception that can be thrown from the CLR will bubble up to the SQL Server context if it’s not caught within the CLR routine. For instance, the sales cross-tab stored procedure could be made a bit more robust by raising an error if yearsMonths is zero characters long, instead of attempting to remove the comma:

if (yearsMonths.Length > 0)
{
//Remove the final comma in the list
yearsMonths.Remove(yearsMonths.Length - 2, 1);
}
else
{
throw new ApplicationException("No data present for the input date range.");
}

Instead of getting a random error from the routine, a well-defined error is now returned—theoretically. In reality, the error isn’t so friendly. As shown in Figure, these errors can get quite muddled. Not only is the error returned as with native T-SQL errors, but the call stack is also included. And although that’s useful for debugging, it’s overkill for the purpose of a well-defined exception.

Standard CLR exceptions aren’t formatted well for readability.

Standard CLR exceptions aren’t formatted well for readability.

A better option might be to use a native T-SQL error, invoked with the RAISERROR() function. A batch can be sent using SqlPipe.ExecuteAndSend(), as in the following code fragment:

if (yearsMonths.Length > 0)
{
//Remove the final comma in the list
yearsMonths.Remove(yearsMonths.Length - 2, 1);
}
else
{
command.CommandText =
"RAISERROR('No data present for the input date range.', 16, 1)";
SqlContext.Pipe.ExecuteAndSend(command);
return;
}

Alas, as shown in Figure, this produces an even worse output. The T-SQL exception bubbles back into the CLR layer, where a second CLR exception is thrown as a result of the presence of the T-SQL exception.

RAISERROR alone doesn’t improve upon the quality of the exception.

 RAISERROR alone doesn’t improve upon the quality of the exception.

The solution, as strange as it seems, is to raise the error using RAISERROR but catch it so that a second error isn’t raised when control is returned to the CLR routine. The following code fragment shows how to accomplish this:

if (yearsMonths.Length > 0)
{
//Remove the final comma in the list
yearsMonths.Remove(yearsMonths.Length - 2, 1);
}
else
{
command.CommandText =
"RAISERROR('No data present for the input date range.', 16, 1)";
try
{
SqlContext.Pipe.ExecuteAndSend(command);
}
catch
{
return;
}
}

After catching the exception, the method returns. If it were to continue, more exceptions would be thrown by the PIVOT routine, as no pivot columns could be defined. Figure shows the output this produces when run with an invalid date range. It’s quite a bit easier to read than the previous exceptions.

Catching the exception in the CLR routine after firing a RAISERROR yields the most readable exception message.

 Catching the exception in the CLR routine after firing a RAISERROR yields the most readable exception message.

The following is the complete code for the sales cross-tab stored procedure, including handling for invalid date ranges:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetSalesPerTerritoryByMonth( SqlDateTime StartDate,
SqlDateTime EndDate)
{
//Get a SqlCommand object
SqlCommand command = new SqlCommand();
//Use the context connection
command.Connection = new SqlConnection("Context connection=true");
command.Connection.Open();
//Define the T-SQL to execute
string sql =
"SELECT DISTINCT " +
"CONVERT(char(7), h.OrderDate, 120) AS YYYY_MM " +
"FROM Sales.SalesOrderHeader h " +
"WHERE h.OrderDate BETWEEN @StartDate AND @EndDate " +
"ORDER BY YYYY_MM";
command.CommandText = sql.ToString();
//Assign the StartDate and EndDate parameters
SqlParameter param =
command.Parameters.Add("@StartDate", SqlDbType.DateTime);
param.Value = StartDate;
param = command.Parameters.Add("@EndDate", SqlDbType.DateTime);
param.Value = EndDate;
//Get the data
SqlDataReader reader = command.ExecuteReader();
//Get a StringBuilder object
System.Text.StringBuilder yearsMonths = new System.Text.StringBuilder();
//Loop through each row in the reader, adding the value to the StringBuilder
while (reader.Read())
{
yearsMonths.Append("[" + (string)reader["YYYY_MM"] + "], ");
}
//Close the reader
reader.Close();
if (yearsMonths.Length > 0)
{
//Remove the final comma in the list
yearsMonths.Remove(yearsMonths.Length - 2, 1);
}
else
{
command.CommandText =
"RAISERROR('No data present for the input date range.', 16, 1)";
try
{
SqlContext.Pipe.ExecuteAndSend(command);
}
catch
{
return;
}
}
//Define the cross-tab query
sql =
"SELECT TerritoryId, " +
yearsMonths.ToString() +
"FROM " +
"(" +
"SELECT " +
"TerritoryId, " +
"CONVERT(CHAR(7), h.OrderDate, 120) AS YYYY_MM, " +
"d.LineTotal " +
"FROM Sales.SalesOrderHeader h " +
"JOIN Sales.SalesOrderDetail d " +
"ON h.SalesOrderID = d.SalesOrderID " +
"WHERE h.OrderDate BETWEEN @StartDate AND @EndDate " +
") p " +
"PIVOT " +
"( " +
"SUM (LineTotal) " +
"FOR YYYY_MM IN " +
"( " +
yearsMonths.ToString() +
") " +
") AS pvt " +
"ORDER BY TerritoryId";
//Set the CommandText
command.CommandText = sql.ToString();
//Have the caller execute the cross-tab query
SqlContext.Pipe.ExecuteAndSend(command);
//Close the connection
command.Connection.Close();
}
};

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics