CLR

Had the opportunity to work with a few CLR stored procedures in SQL server.

Cool stuff.

Here's a quick primer on how to do it, what to do, what not to.

First, something simple, but useful. We've got a need to pass in a string, do "something" to it, then return the modified string.

First, I'll pull together the CLR/.Net code.

Let me put it here, then I'll explain a bit.

using System;

using System.Data;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

public class HelloWorldProc

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void HelloWorld(SqlString x, out SqlString sOut)

{

sOut = x

}

}

The System.Data.SqlTypes is important, as is the SqlServer.Server using statement.

I think ( ? ) the class needs to be in the default namespace. I couldn't get it to work any other way.

The method needs to be static, which does make sense, I think.

Also, note the weird output thing I'm doing. I have to set up the output as a void, then deal with the output as an "output" parameter.

The reason is the limitation placed on Sql CLRs. They can only return integer types (for example, System.Int32, SqlInt32, etc) or voids. Frankly, I've no clue why. Seems silly to me that they can handle a reference parameter, but not an output parameter.

Note that the parameter types are SqlStrings, not strings. But the assignment doesn't even do an explicit typecast.

From the SqlServer side, the assembly has to be created in SqlServer.

CREATE ASSEMBLY HelloWorld from 'c:\temp\HelloWorld.dll' WITH PERMISSION_SET = SAFE;

Next I created a stored procedure to wrap the assembly.

CREATE PROCEDURE SerializeXmlNodes

@x NVARCHAR(1000),

@sOutNVARCHAR(1000) OUTPUT

AS

EXTERNAL NAME HelloWorld.HelloWorldProc.HelloWorld;

The input and output of the stored procedure match the input and output parameters of the .Net method.

Calling the stored proc is done about the same way as calling a "normal" stored proc:

EXEC SerializeXmlNodes @x = 'one', @sOut = @sOut OUTPUT

Select @sOut

in this case, @sOut will be whatever you set @x to, but that's because it's a trivial example.

To get rid of the CLR, you need to drop the stored procedure first, then drop the assembly ("DROP ASSEMBLY HelloWorld").

Very cool stuff, but a little confusing to set it up. Still, it allows string manipulations not available through TSql without serious pain.

--kevin