Stairway to SQLCLR

Stairway to SQLCLR Level 2: Sample Stored Procedure and Function

,

With the introduction of the .NET CLR integration in SQL Server 2005, Microsoft opened the door for us to solve a much larger range of problems within the database. In Level 1 we went over what capabilities are afforded to us through CLR integration that are not present, or at least not as easy, in T-SQL. That information helps us understand what problems are best solved with CLR-based objects. But, that was just theory.

The next step is to solidify that understanding and learn the basic mechanics of the CLR integration (i.e. "SQLCLR"). We will observe some of that theory in practice and see how the various pieces — .NET code, assembly, and T-SQL wrapper objects — fit together. At this point we are not learning how to create any SQLCLR objects. Starting with the next level we will focus on Security, and then Development, and then put it all together as we explore each of the five object types in detail, with a level dedicated to each one.

The goals of this level are to be able to:

  1. increase comfort regarding the idea of using SQLCLR,
  2. understand (at least at a beginner level) most SQLCLR code that you might be reviewing (usually determining if the code is acceptable for Production or if some code found on the interwebs does what you were looking for), and
  3. make better decisions on when it's appropriate to use SQLCLR.

The Code

We will take a look at a Stored Procedure and a Scalar User-Defined Function (UDF) as those are, by, far, the most common types of objects you are likely to encounter or create.  The code presented here is expressly for the purpose of understanding the capabilities of SQLCLR and how the pieces fit together to accomplish those capabilities. The Function and Stored Procedure illustrate many of the capabilities mentioned in Level 1. They are written in C#, though you can use other .NET languages, such as VB.NET and even Visual C++. It is not the intent of this series to teach C# itself, so a beginner-level of understanding of .NET and C# is assumed. However, some basics will be covered as necessary for those who know very little about .NET and C# but still want to understanding what is going on.

The C# source code is presented in fragments so that various concepts can be discussed separately as they come up. For each fragment, there is a brief description of what the code is doing, then the code is presented followed by point-by-point technical details. Within the text of this article, SQL keywords and variables are red while C# keywords and variables are blue. There is an "Additional Reading" section towards the end of the article with links to Microsoft documentation regarding functionality that is discussed in this article.

All of the example code is provided in a zip file attached to end of this article in the "Resources" section. It contains the following files:

  • StairwayToSQLCLR-02-01-EnableCLR.sql – Enables CLR Integration if not already enabled.
  • StairwayToSQLCLR-02-02-InstallSupportingObjects.sql – Creates a [StairwayToSQLCLR] database to contain the example code. This isolates testing from anything else going on. It also creates and populates a table and creates a Stored Procedure, both of which are referenced by the test CLR Function.
  • StairwayToSQLCLR-02-03-InstallCLRObjects.sql – Creates the assembly (which is the compiled .Net code) and the SQL wrapper objects needed to make use of the CLR code.
  • StairwayToSQLCLR-02-04-Examples.sql – the example code, same as what is shown in this article
  • StairwayToSQLCLR-02-05-RemoveExampleDB.sql – Drops the [StairwayToSQLCLR] database to clean up everything.
  • StairwayToSQLCLR-02-06-DisableCLR.sql – If you no longer have a use (or desire) for CLR Integration, run this script to disable it.
  • TheCode.cs – The full C# source code.

Working with the examples requires nothing more than running some T-SQL code. The C# source code is provided to both walk through in this article as well as compile with or without alterations, if you desire. Later in this series we will explore more of the various aspects of development. The example code has been tested on the following versions of SQL Server: 2005 SP3, 2008 R2 SP1, and 2012 SP1.

Enabling the CLR

In order to run any SQLCLR-based code within SQL Server, you need to make sure that the "CLR integration" Server Configuration Option is enabled as it is turned off by default. This can be done in one of two ways:

Surface Area Configuration

  1. In SSMS, right-click on the server in Object Explorer and select “Facets”
  2. In the drop-down list labeled “Facet:” (top, right) select “Surface Area Configuration”
  3. If “ClrIntegrationEnabled” is set to True, just click the “Cancel” button at the bottom
  4. If “ClrIntegrationEnabled” is set to False, change it to True and click the “OK” button at the bottom

T-SQL

The following SQL is provided in the StairwayToSQLCLR-02-01-EnableCLR.sql script:

IF (EXISTS(
       SELECT *
       FROM sys.configurations
       WHERE configuration_id = 1562 -- name = "clr enabled"
       AND value_in_use = 0
       ))
BEGIN
       PRINT 'Enabling CLR...'
       EXEC sp_configure 'clr enabled', 1
       -- Uncomment the WITH OVERRIDE if you get the
       -- "ad hoc changes are not allowed" error
       PRINT 'Reconfiguring...'
       RECONFIGURE --WITH OVERRIDE 
       PRINT 'Done.'
END
ELSE
BEGIN
       PRINT 'CLR already enabled.'
END

For more information on enabling CLR within SQL Server, check out the CLR Integration - Enabling documentation page.

!! Required Work-around for SQL Server 2017 and newer !!

Starting in SQL Server 2017, a new instance-level configuration option was added relating to SQLCLR. It's named "CLR strict security" and forces ALL assemblies, even those with PERMISSION_SET = SAFE, to meet the same requirements as UNSAFE assemblies in order to be created or have code within them be executed. Since this article was written in 2014, the example code and explanations do not account for that new requirement. In order for the examples here to work as expected, you will need to disable the "CLR strict security" setting (if you are using SQL Server 2017 or newer). To do that, please execute the following (only on a dev / test system, not in Production):

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'clr strict security', 0; RECONFIGURE;
GO

To learn more about this unpleasant "security" feature, please see: SQLCLR vs. SQL Server 2017, Part 1: “CLR strict security” – The Problem

 

Setting Up the Example

At this point, if you are going to run the examples locally via the provided scripts, now would be a good time to run the setup scripts.  Assuming you have already run script 01, or otherwise enabled the CLR, you should run scripts 02 and 03 (in that order). The code in those two scripts differs slightly from the code that is shown in the following sections, but any differences are due to making the scripts re-runnable.

Basic Anatomy of SQLCLR

Before looking at any specific code, let's first go over some common elements. The overall structure is that there is .NET source code that is compiled and represented inside of SQL Server in the form of an Assembly. In order to access any code in an Assembly that has been loaded into SQL Server, there needs to be a T-SQL wrapper object (e.g. Stored Procedure, Function, etc.) that points directly to the code in an Assembly and maps the input / output parameters and return value / result set. The following image illustrates these relationships:

PLEASE NOTE: If you are using a namespace in your .NET code, then that gets added to the middle section of the EXTERNAL NAME as follows:

EXTERNAL NAME AssemblyName.[NamespaceName.ClassName].MethodName;

 

The list below details the various parts of .NET and T-SQL that allow for the interaction between them (with regards to SQL Server's CLR integration, of couse, not the typical application interaction).

.NET / CLR side

  • Method Attributes – these specify what type of database object is being created. The attributes have optional parameters which provide information to either the compiler (for its own use or to pass along to SQL Server) or to Visual Studio if it is generating the DDL for you.
  • Input / Output parameters – there are special “Sql” types that map between SQL Server datatypes and their .NET equivalents. For example, “SqlString” or “SqlChars” instead of “string”. The Sql* types have various methods and properties depending on the Sql* type being used. However, they all have: IsNull (boolean indicating NULLness of the passed-in value), ToString() which returns a string representation of the passed-in value , and Value (returns the passed-in value in the associated .NET datatype)
  • Database Operations (optional)
  • DML statements, for example, can be handled in two different ways:
  • “Context Connection” – hooks into the current session (i.e. in-process), just like a T-SQL Stored Procedure, Function, or Trigger would run in. This can be used in SAFE mode, but cannot be used when impersonation is in use.
  • Regular / External connection via standard connection string. This cannot be used in SAFE mode, but can be used when impersonation is in use.
  • Trigger event info can be retrieved via SqlContext.TriggerContext.
  • Returning data (optional) – this depends on the type of object returning the data:
  • Stored Procedure – SqlContext.Pipe is used to send both results as well as information messages (i.e. what shows in the Messages tab in SSMS)
  • Table-Valued Function (TVF) – return a collection (i.e. set of records). Requires an additional method known as the FillRowMethod and which has to be noted in the SqlFunction Method Attribute.
  • Scalar Function – return statements

Database side

  • Assembly – this is where the compiled .NET code lives and is equivalent to a DLL
  • Wrapper Object – this is the native SQL object (Stored Procedure, Function, Trigger, Type, or Aggregate) that maps to .NET code in an Assembly.
  • Login created from an Asymmetric Key or Certificate (optional) – If you need to set an assembly to either EXTERNAL_ACCESS or UNSAFE you need to either set the database option of TRUSTWORTHY to ON (not ideal, which is why it is OFF by default) or make sure that the assembly is signed (a good practice in general) and then create a login based on the same key or certificate that was used to sign the assembly. Logins and TRUSTWORTHY will be discussed in more detail in the Level 3: Security.

General

There are some aspects of implementing SQLCLR objects that are common across all five object types. The following two subsections describe what you will encounter in any SQLCLR project.

Database side

Creating the assembly is done via the CREATE ASSEMBLY statement. An abbreviated version of the statement that is found in the StairwayToSQLCLR-02-03-InstallCLRObjects.sql script is:

Points of Interest in the code above:

  1. Assemblies do not have a Schema.
  2. The name of the assembly will be referenced in the T-SQL wrapper objects.
  3. Assemblies do have an owner as specified by the AUTHORIZATION clause.
  4. Most examples show assemblies being loaded from DLLs on the file system by specifying a path (e.g. FROM 'C:pathAssembly.DLL'). However, as you can see here, you can also provide the binary representation of the DLL in which case the SQL script does not have any external dependencies.
  5. Providing the direct binary representation typically means using a very long line of hex values, and very long lines are not easily managed by SSMS (and sometimes get a warning upon opening the SQL script).  Long lines can be broken up by using a back-slash which continues a line of input on the next line (please see: "Line-Continuation in T-SQL").
  6. If WITH PERMISSION_SET is not specified it will be assumed to be SAFE. Else it can be set to EXTERNAL_ACCESS or UNSAFE.  These values will be discussed in more detail in a future article. The value assigned to the assembly can be changed via ALTER ASSEMBLY.

.NET / CLR side

The following are a few elements that are shared across all .NET, and in some cases all SQLCLR, code.

  1. using statements that denote which code libraries should be checked for methods and enums. These are required only for functionality that is being used (code will not compile if you do not reference the location of functionality being used).
  2. The class definition. All functions (which in turn become any of the SQL object types) are contained within a class. And so there must be at least one class. The name of the class (e.g. Intro as shown below) will be referenced in the SQL wrapper object.
  3. A class will contain one or more methods (i.e. functions).
  4. You can have more than one class.
  5. The class needs to be specified as public so it can be seen outside of the assembly itself. In the context of SQLCLR, the outside world consists of SQL Server and/or another assembly loaded into the same database.
  6. You can have classes marked as private, internal, etc. which contain code that is referenced by the methods exposed to SQL Server, but anything that needs to be exposed to SQL Server needs to be in a class that is marked as public.
  7. The order in which items are defined within a class is irrelevant.
  8. If you use any managed resources then you also need to do proper error handling via try / catch / finally to ensure that those resources are properly closed. Cleaning up resources is very important! These resources are held by the CLR until they are closed and hence can be garbage collected. Not cleaning up a resource (whether an error has occurred or not) will lead to orphaning the reference to that resource such that nothing will be able to close it. If this happens, that resource will be open, and possibly locked, until the CLR host process restarts.  In the case of SQLCLR, the CLR-host process is the SQL Server (i.e. Database Engine) process and it would need to be restarted in order to free up any orphaned, open resources.

Where the Rubber Meets the Road

With all of that in mind, we are now ready to start looking at the example code. The fragment below is the beginning of the file, TheCode.cs, and shows some of the common elements that I just described. To begin with, there is a set of using statements that tell the compiler where to find certain functionality.  We also see that the "Intro" class is being defined and that it is visible to everyone externally due to the "public" setting.

Stored Procedures

The next item we come to in the source code is a method named TestProc that creates a Stored Procedure.  The Stored Procedure accepts a query and returns the results with the option to print the number of columns in the result set to the Messages tab.

The .NET / CLR code

The fragment below shows the declaration of the method that will become the Stored Procedure. This is analogous to

CREATE PROCEDURE ProcName (@InputParam DataType) AS in T-SQL.

Points of Interest in the code above:

  1. The method attribute (line in square-brackets with SqlProcedure in it) tells the compiler that this method is going to use the Stored Procedure API as opposed to the Function or Trigger, etc interfaces. The attribute itself is required but it has some optional parameters and the parameters it can take vary slightly depending on whether it is a Stored Procedure, Function, etc.  Some of the parameters are merely to assist in the automated deployment functionality of Visual Studio (such as Name shown above) while others are directives to SQL Server (we will see an example of this when we look at Functions).
  2. A visiblity of public is again required for SQL Server to see and call this method.
  3. The method must be static in order for SQL Server to see it.
  4. Since this C# method is being used to create a Stored Procedure, there are only two options with regards to the return type: void which returns 0, or SqlInt32 which sends back the standard INT return value.
  5. The actual name of the method, TestProc, will be referenced in the T-SQL wrapper object. When using the deployment feature of Visual Studio, if the Name property of the SqlProcedure attribute is not specified, the name of the method will be used as the Stored Procedure name (e.g. CREATE PROCEDURE TestProc), else the value of the Name property will be used (e.g. CREATE PROCEDURE StairwayToSQLCLR_02_TestProc).
  6. Datatypes are not exactly the same between SQL Server and .NET languages so a series of types is provided to handle things such as NULL, streaming options for NVARCHAR(MAX) and XML, etc.  Here you can see SqlString which typically maps to NVARCHAR(1 – 4000) but can map to NVARCHAR(MAX) and SqlBoolean which maps to BIT.
  7. The SqlFacet attribute is an optional hint to Visual Studio when generating the DDL for the T-SQL wrapper object to use. By default, SqlString maps to NVARCHAR(4000) but can be overriden using MaxSize which takes a value of 1 – 4000 or -1 to denote MAX.

The fragment below does minor validation of the @Query input parameter and simply exits if no query text is passed in.  Then the database connection is defined by specifying a special SQLCLR connection string.

Points of Interest in the code above:

  1. Each of the SQL <-> CLR wrapper types, such as SqlString, has a boolean IsNull property.
  2. By using only "context connection = true" as the connection string, the .NET code will run in the current session (i.e. the session in which this code is called), just like Stored Procedures typically do.
  3. An even more direct way to run a query is to use SqlContext.Pipe.ExecuteAndSend, but that does not allow you to intercept the results. Here we want to intercept the results so we can get the number of columns in the result set.

The fragment below creates the command (i.e. query) that will be executed.  It then connects to the database (via Open) and executes the query with the expectation of getting results back (via ExecuteReader). This is done within a try (part of try-catch-finally) so if an error occurs we have a chance to handle it gracefully.

By using ExecuteReader, we have the choice of either stepping through the results one row at a time (like a Cursor)—possibly to do manipulation or track some data about the results—or to simply pass the SqlDataReader to SqlContext.Pipe.Send to handle moving the results from SQL Server to the caller. We will cover sending rows back individually when we focus on Stored Procedures in a future article.

The fragment below checks the input parameter, @PrintFieldCount, to determine whether or not to display the number of fields / columns in the Messages tab. It then sends the result set back to the calling process so it can be returned to the user.

Points of Interest in the code above:

  1. We make use of the SqlBoolean wrapper type, PrintFieldCount, to test for IsTrue without needing to first test for IsNull. For most other types you need to first test for IsNull because trying to access the Value property (which returns the value in the native .NET type) will get an error if a NULL was passed in.
  2. SqlContext.Pipe.Send(string) is used to mimic a PRINT statement to send text to the Messages tab
  3. Because we used a SqlDataReader and did an ExecuteReader (shown in the prior fragment) instead of SqlContext.Pipe.ExecuteAndSend, we have access to the result set to find out how many columns are in it (i.e. the FieldCount property). This is not something that can be done in T-SQL!
  4. SqlContext.Pipe.Send(SqlDataReader) is used to send the result set to the caller without having to loop through each row.
  5. Another option – sending a dynamically structured result set, one row of the SqlDataReader at a time – allows for even greater flexibility in terms of intercepting the results as they are passed back. That option will be explored in the Level focusing on Stored Procedures. And that is definitely not something that can be done in T-SQL!

The fragment below is mainly just standard error handling and resource cleanup.  The catch block prints a custom message and then re-throws the error to the calling process. The finally block prints a custom messae and closes the two resources we used in the try block above. If resources are not closed then they cannot be deallocated by the Garbage Collector -- the .NET memory cleanup process -- and if they are file handles (in cases where you open files for reading and/or writing) then the locks (if any exist) on those files will not be released. When the method returns / exits, resources (memory, file locks, etc.) that were not cleaned up are orphaned and will remain in use until either SQL Server or Windows is restarted.

Points of Interest in the code above:

  1. The finally section is part of try / catch / finally and that block of code is executed whether or not an error is caught. This allows for specifying certain operations, such as resource cleanup, that have to be done regardless of success or failure, only once.
  2. SqlContext.Pipe.Send("string") is used to print messages so it is easy to see which parts of the code are executing when the Stored Proc errors or ends successfully.
  3. Unlike RAISERROR in a CATCH block, throw will halt execution as well as raise the error to the calling process. However, code within a finally block will still execute after a throw, but execution of code within the method will end when the finally block is done. THROW was introduced into T-SQL in SQL Server 2012 and just like throw in C#, it will halt execution of the batch.
  4. We use return without specifying a number since we declared the method with a return type of void.

The T-SQL Code

Now let’s look at the T-SQL wrapper Stored Procedure (found in the StairwayToSQLCLR-02-03-InstallCLRObjects.sql script). This object merely provides a SQL interface to the .NET code in the Assembly.

Points of Interest in the code above:

  1. The name of the Stored Proc matches the Name property of the SqlProcedure method attribute and not the name of the method.
  2. The @PrintRowCount input parameter has a default of NULL.  This is not settable via the .NET code but can be added manually.
  3. The EXTERNAL NAME clause references:
    1. StairwayToSQLCLR-02-Example – the name of the assembly as defined by the CREATE ASSEMBLY statement
    2. Intro – the name of the class within the C# code (i.e. the public class Intro line)
    3. TestProc – the name of the method within the Intro class. This is the actual method name and has nothing to do with the value of the Name property of the SqlProcedure method attribute.

Test Drive the Stored Procedure

This example shows a simple SELECT which prints out the number of returned fields in the Messages tab.

EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestProc
       @Query =  N'SELECT Tab.Num, 44 AS [OtherNum] FROM
                     (SELECT 1 UNION ALL SELECT 23 UNION ALL SELECT 3423) Tab(Num)',
       @PrintFieldCount = 1

 

The following two examples show that displaying the number of returned fields in the Messages tab is both optional and dynamic.  In the first example below, the second input parameter, @PrintFieldCount, is not passed in and has a default of 0, whereas the previous and next examples pass in 1.  In the second example below we see that the field count truly is dynamic as it is not the same number of fields as in the previous example: field count is 2 in the previous example but is higher when selecting from sys.columns as shown below.

EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestProc
      'SELECT * FROM StairwayToSQLCLR.sys.columns'
-- No field count in Messages tab

EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestProc
      'SELECT * FROM StairwayToSQLCLR.sys.columns', 1
-- field count in Messages tab

These following two examples show the error handling working.  Notice how we see the “Cleaning up...” message from the finally block but not the “Ending...” message. The specific error message returned from SQL Server is underlined in yellow in both cases. The first error is due to referencing a non-existent object whereas the second error is due to misspelling a SQL command.

EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestProc
      'SELECT * FROM sys.columnsd'


EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestProc
      'SgELECT * FROM sys.columns'

User-Defined Functions

The next item we come to in the source code is a scalar User-Defined Function (UDF). It accepts a table name to get the number of rows in it, either by Dynamic SQL or calling a Stored Procedure, and has an option to insert a new row into a specific table.

The .NET / CLR code

The fragment below shows the declaration of the method that will become the User-Defined Function. This is analogous to

CREATE FUNCTION FuncName (@InputParam DataType) RETURNS DataType AS in T-SQL.

Points of Interest in the code above:

  1. The method attribute (i.e. SqlFunction) tells the compiler to use the Function API.  Like the attribute for Stored Procedures, it has optional properties. These will be discussed in more detail when we focus on User-Defined Functions later in this series.
    1. The Name property is only for Visual Studio if / when it creates the DDL.  If Name isn’t specified then Visual Studio will use the name of the method for the name of the corresponding T-SQL wrapper function.
    2. The IsDeterministic and IsPrecise properties are hints for the Query Optimizer and are false by default. There are rules for when to set these values to true or false and it is up to the developer to set them properly. Setting them incorrectly can adversely affect Indexed Views and Computed Columns, if CLR-based functions are being used for such things.
    3. The DataAccess and SystemDataAccess parameters are security settings for SQL Server that prevent or allow the function from accessing user or system data respectively and are set to None by default.
  2. We again see this method marked as public and static, just like the Stored Procedure we looked at first.
  3. Because this is a scalar function, the return type (in this case SqlInt64) can be any of the special Sql* wrapper types.

The fragment below validates one of the input parameters and sets up the connection using the same special connection string that we used in the Stored Procedure. Notice that the validation of the input parameter incorrectly allows 10 though (CountMethod.Value > 10 instead of > 9).  This will enable us to pass a value of 10 through to the T-SQL Stored Procedure (not the wrapper object; the one named StairwayToSQLCLR_02_GetNumRows, which is shown in the "T-SQL Code" section below) that has its own validation which correctly errors if the value of @CountMethod is greater than 9.

Points of Interest in the code above:

  1. The connection is using “context connection = true” so it will be in-process (i.e. same SPID / Session).
  2. Please note the beginning of a “try / catch” block.  Using Try / Catch is not something that can be done in T-SQL functions!

The fragment below checks the @CountMethod input parameter to determine which of three methods to use to get the row count: SELECT from a system table, SELECT from the table itself, or EXEC of a Stored Procedure that contains several other options. The first option -- querying a system table -- submits a dynamically constructed query that inserts the value of the @TableName input parameter directly into the query (i.e. this is not a parameterized query). This is equivalent to calling EXEC (@QueryText) instead of using sp_executesql and passing in parameters.

Points of Interest in the code above:

  1. switch in C# is equivalent to CASE {Expression} in T-SQL.
  2. case in a C# switch is equivalent to WHEN in T-SQL.
  3. We are create some dynamic SQL by inserting the value of an input parameter into a query.
  4. The Trim function in C# trims both ends, much like doing LTRIM(RTRIM(string)).
  5. The CommandType of Text indicates that this is a full query and not a Stored Procedure call (i.e. RPC).
  6. Please note that building a query in this manner should not be done as it is suseptable SQL Injection and this query could have been parameterized. But here I am just illustrating a point: using Dynamic SQL is not something that can be done in T-SQL functions!
  7. Keep in mind that the SystemDataAccess property of the method attribute (the SqlFunction line just above the method) was set to None.

The fragment below handles the second option -- querying the table itself -- and also submits a dynamically constructed query that inserts the value of the @TableName input parameter directly into the query text.

Points of Interest in the code above:

  1. This section is essentially the same as the section above.
  2. The only difference in @CountMethod = 2 is that here we query the table directly rather than going to a system table.
  3. Keep in mind that the DataAccess property of the method attribute (the SqlFunction line just above the method) was set to Read.

The fragment below handles the remaining options, all of which call the same Stored Procedure, passing in both input parameters so that the Stored Procedure can figure out which method to use based on the value of @CountMethod. The definition of the StairwayToSQLCLR_02_GetNumRows Stored Procedure is shown below in the "T-SQL Code" section.

Points of Interest in the code above:

  1. default in a C# switch is equivalent to ELSE in a T-SQL CASE.
  2. The other options for the @CountMethod input parameter (3 - 9) all call a T-SQL Stored Procedure.
  3. So as to not distract from our focus on the C# side of things, the definition of the Stored Procedure will be shown after the code for the T-SQL wrapper Function, just before the “Test Drive” section.
  4. This is the typical way of calling Stored Procedures from .NET code: map the parameter to a SqlParameter and save it in the collection (i.e. set / array) of parameters associated with the command (i.e. sqlCommand.Parameters).
  5. The other values for ParameterDirection are: Output, InputOutput, and ReturnValue.
  6. Calling a Stored Procedure, at least directly (remember, we are using the Context Connection so this call is in-process), is not possible in T-SQL! It is possible to exec a Stored Procedure in a T-SQL function using OPENQUERY / OPENROWSET, but that requires enabling “Ad Hoc Distributed Queries”. In addition to that, as of SQL Server 2012, OPENQUERY / OPENROWSET are much pickier about what is being done in any procs that are being called, hence you might need to use WITH RESULT SETS and specify the result set structure.

The fragment below connects to the database and executes the Stored Procedure. The rest is standard error handling and resource cleanup (described in more detail above).

In the code above, ExecuteScalar simply retrieves the first column of the first row returned by the query.

The fragment below checks the @InsertRow input paramater to determine whether or not to insert a row into the test table that was created in the StairwayToSQLCLR-02-02-InstallSupportingObjects.sql script. If "true", a connection is defined using a regular / external connection string as opposed to the internal context connection. Dynamic SQL is constructed (including a natively generated GUID), the connection is establed, and the SQL is executed.

Points of Interest in the code above:

  1. Just like T-SQL functions, in-process connections (i.e. Context Connection = true) cannot change the state of the database.
  2. Here we are making an external connection to a SQL Server to work around the state-change limitation. For this example it is pointing to the local machine but it could be a remote machine.
  3. Clearing the StringBuilder is done by setting the Length property to 0 rather than calling the Clear method as Clear was not available until .NET 4.0 and I am using .NET 2.0 here so to be fully compatible down to SQL Server 2005.
  4. We create and execute a side-effecting query: an INSERT statement. This is cannot be done in a T-SQL function without using OPENQUERY / OPENROWSET!
  5. Table definition is: ID UNIQUEIDENTIFIER NOT NULL, InsertTime DATETIME NOT NULL.
  6. StringBuilder is an efficient way of building a string.
  7. Guid.NewGuid() works just like NEWID does in T-SQL. This cannot be done directly in a T-SQL function, but can be obtained by first encapsulating “SELECT NEWID() AS [NewGUID]” in a View and selecting from that View in the function (same goes for the RAND function).
  8. ExecuteNonQuery runs the SQL but does not allow for any scalar value or result set to be returned.

The fragment below does the usual error handling and resource cleanup. One difference from the previous error handling examples is that there are two catch blocks: the first for a specific type of exception, and the second for all other exceptions. Trapping the SecurityException and testing for the specific requested permission lets us tailor the error message to be useful.

The T-SQL Code

Now let’s look at the T-SQL wrapper Function (found in the StairwayToSQLCLR-02-03-InstallCLRObjects.sql script):

Points of Interest in the code above:

  1. The name of the Function matches the Name property of the SqlFunction method attribute and not the name of the method.
  2. The input parameters are not explicitly mapped to the input parameters of the .NET method. They are implicitly mapped by position and the datatypes are validated against the ones in the .NET method for the same position. Changing the datatype will result in an error, though changing the width / max size of a NVARCHAR type will not cause an error. The names of the parameters are not validated so you can change them in the T-SQL wrapper object and the function will still work.
  3. The return type BIGINT matches the SqlInt64 return type of the method (i.e. public static SqlInt64 TestFunc().
  4. The function option of RETURNS NULL ON NULL INPUT can help avoid the inefficiency of calling code that doesn’t need to process. It is a short-cut that tells SQL Server if ANY of the input parameters are set to NULL then simply return NULL and don’t even bother running the function. Sweet!
  5. The EXTERNAL NAME clause references:
    1. StairwayToSQLCLR-02-Example – the name of the assembly as defined by the CREATE ASSEMBLY statement
    2. Intro – the name of the class within the C# code (i.e. the public class Intro line)
    3. TestFunc – the name of the method within the Intro class. This is the actual method name and has nothing to do with the value of the Name property of the SqlFunction method attribute.

Create and populate the T-SQL table that is referenced by the SQLCLR function (found in the StairwayToSQLCLR-02-02-InstallSupportingObjects.sql script):

CREATE TABLE dbo.StairwayToSQLCLR_02 (
                                       ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
                                       InsertTime DATETIME NOT NULL
                                     )
INSERT INTO dbo.StairwayToSQLCLR_02 (ID, InsertTime)
       SELECT TOP 100 NEWID() AS [ID], GETDATE() AS [InsertTime]
       FROM   sys.all_columns

Finally, create the T-SQL Stored Procedure (found in the StairwayToSQLCLR-02-02-InstallSupportingObjects.sql script):

/*
@CountMethod options:
3 = check @TableName via system table
4 = check @TableName directly via Dynamic SQL
5 = check StairwayToSQLCLR_02 directly in regular SQL without SET NOCOUNT or NEWID()
6 = check StairwayToSQLCLR_02 directly in regular SQL but attempt SET NOCOUNT ON
7 = check StairwayToSQLCLR_02 directly in regular SQL but attempt using NEWID()
8 = check #Temp temp table
9 = return CONTEXT_INFO() converted to BIGINT
*/CREATE PROCEDURE dbo.StairwayToSQLCLR_02_GetNumRows
(
       @TableName SYSNAME,
       @CountMethod TINYINT = 3 -- check via system table
)
AS
IF (@CountMethod NOT BETWEEN 3 AND 9)
BEGIN
       RAISERROR(N'@CountMethod needs to be between 3 and 9.', 16, 1)
       RETURN
END

IF (@CountMethod = 3)
BEGIN
       SELECT SUM([rows]) AS [TotalRows]
       FROM sys.partitions sp
       WHERE sp.[object_id] = OBJECT_ID(@TableName)
       AND sp.index_id < 2
       RETURN
END
IF (@CountMethod = 4)
BEGIN
       DECLARE @SQL NVARCHAR(4000)
       SET @SQL = N'SELECT COUNT_BIG(*) AS [TotalRows]
              FROM ' + @TableName + N' WITH (NOLOCK)'
       EXEC(@SQL)
       --EXEC sp_executesql @SQL
       RETURN
END
IF (@CountMethod = 6)
BEGIN
       SET NOCOUNT ON
       --SET DATEFIRST 3
END
IF (@CountMethod = 7)
BEGIN
       DECLARE @NewGUID UNIQUEIDENTIFIER
       SET @NewGUID = NEWID()
END
IF (@CountMethod = 8)
BEGIN
       SELECT COUNT_BIG(*) AS [TotalRows]
       FROM #Temp
       RETURN
END
IF (@CountMethod = 9)
BEGIN
       SELECT COALESCE(CONVERT(BIGINT, CONVERT(VARBINARY(5), CONTEXT_INFO())), -1) AS [TotalRows]
       RETURN
END

-- @CountMethod = 5 skips the IF blocks and only runs the following:
SELECT COUNT_BIG(*) AS [TotalRows]
FROM dbo.StairwayToSQLCLR_02 WITH (NOLOCK)
GO

Points of Interest in the code above:

  1. The proc is designed to get the number of rows in a given table using different methods.
  2. CountMethods of 3 and 4 are more flexible but not allowed for different reasons:
    1. CountMethod = 3 is not allowed only due to our setting of the SystemDataAccess property of the SqlFunction method attribute to None.
    2. CountMethod = 4 errors due to EXEC and sp_executesql not being allowed when called in-process from a CLR function (i.e. using the Context Connection).
  3. CountMethods of 5 - 7 need to have a hard-coded table reference and thus ignore the @TableName parameter.
    1. CountMethod = 6 attempts setting NOCOUNT, and all SET commands are invalid for in-process Stored Procedure calls from SQLCLR functions.
    2. CountMethod = 7 attempts calling NEWID, and both NEWID and RAND are invalid for in-process Stored Procedure calls from SQLCLR functions.
  4. CountMethods of 8 and 9 both work and show that the Stored Procedure truly is being called in-process. Meaning, they access session-specific items that will show if the connection made from the SQLCLR function is tapping into the same session that the SQLCLR function is being called from, or if a new connection is being made.
  5. The RAISERROR is also not valid when called in-process from a SQLCLR function, but we have error handling in the C# code that validates the @CountMethod parameter and should not call this Stored Procedure for any value outside of the valid range. However, there is that intentional bug that allows a value of 10 to go through.

Test Drive the Function

Within each section, run each query separately. The results are listed in a SQL comment after each query.

The following section demonstrates in-process (i.e. Context Connection) usage of Dynamic SQL. The only real difference between queries 2 and 3 is whether the Dynamic SQL looks at a system table or the table passed in as the first parameter. We had declared the SQLCLR function with a value of None for the SystemDataAccess property so it is not allowed to access sys.partitions, even if the login has SELECT access to it.

SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 0, 0)
-- error: "Invalid @CountMethod value: 0"
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 1, 0)
-- error: "This statement has attempted to access data whose access is restricted by the assembly"
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 2, 0)
-- 100
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02222', 2, 0)
-- error: "Invalid object name 'StairwayToSQLCLR_02222'"

The following section demonstrates an in-process call to a regular T-SQL Stored Procedure (not allowed in T-SQL functions; only calls to Extended Stored Procedures are allowed). Calling a Stored Procedure in-process does not side-step the other T-SQL UDF restrictions.

SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 3, 0)
-- error: "This statement has attempted to access data whose access is restricted by the assembly"
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 4, 0)
-- error: "Invalid use of a side-effecting operator 'EXECUTE STRING' within a function"
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 5, 0)
-- 100
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 6, 0)
-- error: "Invalid use of a side-effecting operator 'SET ON/OFF' within a function"
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 7, 0)
-- error: "Invalid use of a side-effecting operator 'ASSIGN' within a function"

The following section demonstrates another aspect of in-process calls to read-only T-SQL Stored Procedure (again, not allowed in T-SQL functions): access to session-based objects and data.

While using OPENQUERY / OPENROWSET to call a Stored Procedure from within a Function would have access to Global Temp Tables, they are not always a viable option due to not having per-process separation. But as we see in the following test, Local Temp Tables are accessible.

-- run the following 2 statements once to set up for @CountMethod = 8
CREATE TABLE #Temp (Col1 INT)
INSERT INTO #Temp
        ( Col1
        )
        SELECT 123
        UNION ALL
        SELECT 3423432
        UNION ALL
        SELECT 23
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(                             
       N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 8, 0) 
-- 3
INSERT INTO #Temp
        ( Col1
        )
        SELECT 4
        UNION ALL
        SELECT 5
        UNION ALL
        SELECT 6
        UNION ALL
        SELECT 7
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc( 
       N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 8, 0) 
-- 7

The following test shows accessing session-specific data. CONTEXT_INFO is often handy for passing information to Triggers.

-- CONTEXT_INFO() will return NULL if never set, but cannot be set to NULL
SELECT CONTEXT_INFO()
SET CONTEXT_INFO 0x00
SELECT CONTEXT_INFO()
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 9, 0)
-- 0
SET CONTEXT_INFO 0x02223344
SELECT CONTEXT_INFO() -- 0x0222334400000000000000...
SELECT CONVERT(VARBINARY(5), CONTEXT_INFO()) -- 0x0222334400
SELECT CONVERT(BIGINT, CONVERT(VARBINARY(5), CONTEXT_INFO())) -- 9163719680
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 9, 0)
-- 9163719680

The following section provides the control for the experiment. Running the T-SQL Stored Procedure outside of the context of the CLR function shows that the syntax and operation of each of those methods produces no errors, except for the last line which produces the expected error. We don't need to test @CountMethod = 5 as that option worked via the SQLCLR function.

EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_GetNumRows
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 3
EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_GetNumRows
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 4
EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_GetNumRows
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 6
EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_GetNumRows
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 7
-- All four of the above return: 100
EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_GetNumRows
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 8
-- 7
EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_GetNumRows
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 9
-- 9163719680
EXEC StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_GetNumRows
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 10
-- error: @CountMethod needs to be between 3 and 9.

The final test section below demonstrates using a remote / external DB connection to allow for side-effecting code (i.e. changing the state of the database).

Running the following SELECT should produce the “System.Security.SecurityException” error shown directly following the SQL code. The error is due to the Assembly being created as SAFE but attempting to make an external connection (i.e. a regular database connection instead of the internal context connection).

-- Attempt to insert a row via @InsertRow = 1
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 2, 1) AS [RowCount]

Later in this series we will explore Assembly security in more detail. Setting a database to TRUSTWORTHY ON is certainly not ideal, but it suffices for this example and is another reason for having a separate test database. For now, just run the two ALTER statements shown in the error message (shown above) to allow code within the Assembly to reach outside of SQL Server (even if only to come right back into the same instance).

Running the function again should succeed this time. However, the returned row count was taken before the INSERT so it will not reflect the new row count post-INSERT.

-- Attempt to insert a row via @InsertRow = 1
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 2, 1) AS [RowCount]

Getting just the row count again will show the updated number of rows.

-- display new row count ; no new INSERT
SELECT StairwayToSQLCLR.dbo.StairwayToSQLCLR_02_TestFunc(
      N'StairwayToSQLCLR.dbo.StairwayToSQLCLR_02', 2, 0) AS [RowCount]

Running the following SELECT will show that the first 100 rows were inserted when the setup script was run but the newest row (row #1 in the result set) will be more recent.

-- display effect of using @InsertRow = 1
SELECT * FROM dbo.StairwayToSQLCLR_02 ORDER BY InsertTime DESC

TVFs

For an example of SQCLR Table-Valued Functions, please see my earlier article on that topic, CLR Table-Valued Function Example with Full Streaming (STVF / TVF).

Conclusion

At this point you should be familiar with the basic components of SQLCLR-based objects: the .NET source code (including the Sql* wrapper types, getting in-process data via the Context Connection, and passing back data via SqlContext.Pipe), the compiled Assembly, and the T-SQL wrapper objects. We looked at how these various pieces fit together and some of the configurable options in both the .NET and T-SQL code. Finally, we observed the behavior of these elements and options, highlighting several of the unique capabilities afforded by SQLCLR-based code that are not present (or nearly as easy) in T-SQL objects. Hopefully you are now more comfortable with the concept of SQLCLR integration and better understand how and when it might benefit you.

Additional Reading

.NET / C#:

T-SQL:

About the Author

Solomon Rutzky has been working with databases for since 1996, focusing on SQL Server since 2002. He has written several articles for SQL Server Central, including the Stairway to SQLCLR series, and SimpleTalk, and has a blog: https://SqlQuantumLeap.com/ . Solomon is owner / founder of Sql Quantum Lift ( https://SqlQuantumLift.com/ ), and is the creator of the popular SQLCLR library of functions: SQL# ( https://SQLsharp.com/ ). He does what he can to help others by answering questions on a couple of StackExchange sites ( https://stackexchange.com/users/281451/ ), Ask.SqlServerCentral.com/users/20164/SRutzky.html , and SqlServerCentral.com/Forums/ .

 

This article is part of the parent stairway Stairway to SQLCLR

Resources

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (6)

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (6)

You rated this post out of 5. Change rating

Related content