Excited about the .NET code coming to SQL Server? Discover how to call a Whidbey function from Yukon and see where this capability might come in handy.

You probably saw some of the news coming out of Microsoft's recent Professional Developers Conference. Microsoft showed off three major products that will ship over the next two to three years:

• "Longhorn", the next major version of Windows.
• "Whidbey", the next version of Visual Studio .NET.
• "Yukon", the next version of SQL Server.

Of course, all three of these products can work together. In particular, SQL Server "Yukon" can execute code written with Visual Studio .NET "Whidbey". But how does that work? In this article, I'll show you the basics of calling a Whidbey function from Yukon, and help you think about where this capability might come in handy.

A: Writing the Code

Yukon can execute four different types of .NET routines:

• Scalar-valued user-defined functions
• Table-valued user-defined functions
• User-defined procedures
• User-defined triggers

The simplest of these .NET routines is the scalar-valued user-defined function. "Scalar-valued" means that it returns a single scalar type such as an integer or a string. The function can have zero or more inputs. Such functions are implemented in .NET as static methods of a class. For example, here's some VB .NET code for a class that exposes two functions that can be used as scalar-valued user-defined functions:

Namespace SimpleMath
    Public Class Conversions
        Public Shared Function MilesToKm(ByVal Miles As Double) As Double
            MilesToKm = 1.609344 * Miles
        End Function

        Public Shared Function KmToMiles(ByVal Kilometers As Double) As Double
            KmToMiles = 0.621371192 * Kilometers
        End Function
    End Class
End Namespace

Remember, Visual Basic .NET uses the Shared keyword to indicate a static method.

As a first step, then, I saved this code in a Visual Basic .NET class library named SimpleMath. I set the assembly name to SimpleMath and the root namespace to an empty string (you can make both of these settings in the Assembly section of the project's property pages). Compiling this assembly gives me code that I can call from SQL Server...after a bit more work.

B: Registering the Functions

The next step is to tell SQL Server that these functions exist, and that it's OK to call them. SQL Server can't just execute arbitrary code in any old .NET asembly. With the advent of Yukon, you'll discover T-SQL extensions to handle this task. First, you need to register the assembly:

CREATE ASSEMBLY SimpleMath
FROM 'C:\SimpleMath1\SimpleMath1\bin\SimpleMath.dll'

Next, you need to tell SQL Server about the particular functions within the assembly. This requires one call to CREATE FUNCTION for each function in the assembly:

CREATE FUNCTION MilesToKm(@Miles FLOAT)
RETURNS FLOAT
EXTERNAL NAME SimpleMath:[SimpleMath.Conversions]::MilesToKm
GO
CREATE FUNCTION KmToMiles(@Kilometers FLOAT)
RETURNS FLOAT
EXTERNAL NAME SimpleMath:[SimpleMath.Conversions]::KmToMiles
GO

The CREATE FUNCTION call may be familiar from SQL Server 2000, where you can use it to create a T-SQL function. The new syntax is the EXTERNAL NAME clause, which specifies where to find the function in this format:

AssemblyName:[Namespace.Class]::Method

The assembly name here, of course, is the one that was registered with the CREATE ASSEMBLY call.

C: Calling the Functions

All that's left is to call the functions. This uses the same syntax that already exists for calling user-defined functions in T-SQL:

SELECT dbo.MilesToKm(27.5), dbo.KmToMiles(42)

And here are the results:

---------------------- ----------------------
44.25696               26.097590064

(1 row(s) affected)

That's it! Although the internal plumbing to use CLR functions from T-SQL must be quite complex, the SQL Server team has done a great job of making the syntax easy to use.

So What Good is It?

Of course, implementing these simple conversion functions in VB .NET, while a good demonstration of how to hook everything up, doesn't really make a lot of sense. SQL Server is perfectly capable of doing multiplication itself, and implementing these functions in T-SQL would involve less overhead. But there are many things you can do with the .NET languages that are difficult or impossible in T-SQL. Here are a few reasons that you might actually call .NET code from SQL Server:

• To work with the event log, WMI objects, or other system resources
• To use the cryptographic functions built into the .NET Framework Class Library
• To perform complex mathematical calculations
• To move SQL Server data to Web pages or disk files

With the advent of .NET code in SQL Server, it's going to be possible to perform a vast array of new tasks from within SQL Server applications. There's a lot more to this capability than I've shown you here; in particular, I've ignored both the well-designed security system and the ways in which .NET code can call back into SQL Server to obtain more data. If you're a SQL Server developer, you should definitely keep an eye on this stuff as "Yukon" progresses from beta code to release.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his MCAD 70-305, MCAD 70-306, and MCAD 70-310 Training Guides from Que Publishing. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.