First attempts at SQL Server 2005 CLR Stored Procedures

Having just returned from the "Get ready for SQL Server 2005 roadshow", listening to Niels Beglund's excellent presentaion, and also hearing that there is a possibility that we might actually be migrating to SQL Server 2005, I thought it might be a good idea to try and write a .NET CLR stored procedure for myself. These things always look easy when they are demonstrated, but never seem so easy when you do them for yourself. However, this time, I was proved wrong, but I'm recording it here in case it's more difficult when I need to do it again and to help anyone else trying to do the same thing:

Here's what I did:

1. Write your function
Create a class library project containing a public and static function. I wrote the following to convert someone's name into having an upper case first character and lower case characters after that. CLR stored procedures are good for string handling, and often people enter their names in a database in an inconsistent format.

public class NameProcessor
{
 public static String ProcessName
(String name)
 {
  String rv;
  rv = name.Substring(1,1).
ToUpper() + name.Substring(2).ToLower();
  return rv;
 }
}

I know there is a mistake in this, but we'll debug it later.
2. Compile this.
3. Go into SQL Server 2005 Management Studio
4. Create an Assembly

create assembly processstring from 
‘c:\Documents and Settings\Richard\My 
Documents\...\classlibrary1.dll’

5. Check it exists

select * from sys.assemblies

6. Map an SQL function to your function

Create function dbo.pn(@name nvarchar(100))
returns nvarchar(100)
external name processstring.NameProcessor.ProcessName

Note this has to use an nvarchar type
7. Check it works

select dbo.pn('abc')

Oops - this returns 'Bc', not what we wanted
8.Try and debug it
Add the PDB file to your assembly:

alter assembly processstring
add file from 'c:\Documents and Settings\Richard
My Documents\...\classlibrary1.pdb'

9. Debug in Visual Studio

• Select "Attach to Process" from the tools menu


• Select "Show Processes from all users" in the bottom left.


• Select "sqlservr.exe", and select "Attach to Managed Code"


• Add a breakpoint in your function

10. Check your breakpoint is fired
Go back into Management Studio

select dbo.pn('abc')

Your breakpoint is hit. Correct the code.
As this is .net, you can add a unit test for
this with nUnit if you're so inclined.
11. Update your assembly
Drop your PDB File from your assembly

alter assembly processstring
drop file 'c:\Documents and Settings\Richard
My Documents\...\classlibrary1.pdb'

Update your DLL

alter assembly processstring from 
'c:Documents and Settings\Richard
My Documents\...\classlibrary1.dll'

12. Run the function

select dbo.pn('abc')

Excellent - this now works correctly.