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 functionCreate 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 Studio4.
Create an Assemblycreate assembly processstring from
‘c:\Documents and Settings\Richard\My
Documents\...\classlibrary1.dll’
5.
Check it existsselect * from sys.assemblies
6.
Map an SQL function to your functionCreate function dbo.pn(@name nvarchar(100))
returns nvarchar(100)
external name processstring.NameProcessor.ProcessName
Note this has to use an nvarchar type7.
Check it worksselect dbo.pn('abc')
Oops - this returns 'Bc', not what we wanted
8.
Try and debug itAdd 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 firedGo 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 assemblyDrop 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 functionselect dbo.pn('abc')
Excellent - this now works correctly.
