This post originated from an RSS feed registered with .NET Buzz
by Richard Jonas.
Original Post: First attempts at SQL Server 2005 CLR Stored Procedures
Feed Title: Richard Jonas
Feed URL: http://feeds.feedburner.com/blogspot/ouNA
Feed Description: Richard Jonas's blog about .NET, web development and agile methodologies.
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'
Previous Topic
1. Write your function