How to invoke .NET Assembly from SQL Server 2005

Steps

1. Write a C# assembly which will have a simple method called "HelloWorld" which will take a string as argument and will return the string concatenated with "Hello".

2. Build the assembly

3. Open SQL Query Analyzer and


CREATE ASSEMBLY asmHelloWorldSQL FROM 'c:\HelloWorldSQLAssembly.dll'

Note: asmHelloWorldSQL is name of assembly which can be anything and we have to pass the path where the assembly resides

4. Create a function which will reference the assembly we created


CREATE FUNCTION dbo.clrHelloWorld
(
@name as nvarchar(200)
)
RETURNS nvarchar(200)
AS EXTERNAL NAME [asmHelloWorldSQL].[HelloWorldSQLAssembly.Class1].[HelloWorld]

Note: This function will invoke the HelloWorld method from the assembly and return the string.

5. SELECT dbo.clrHelloWorld('Invoke from SQL 2005')
and,
this will return

"Hello Invoke from SQL 2005"

Happy assembling :-)