Sample to use CLR for SQL2005

Below is a summary of steps to create CRL for SQL2005

1. Create a project using Visual Studio 2005 (Create DLL with “User Defined Function” (DLL: SQL_SimpleCLR.dll))
2. Click to add “User-defined function”
3. Build and copy the DLL under “..\binn” of SQL Server
[Sample source code]
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString DispTime(SqlString HashString)
{
// Put your code here
string hashedPasswordString = null;
hashedPasswordString = "Hello";

//return new SqlString("Hello");
return new SqlString(hashedPasswordString);
}
};

4. Execute the following command to register addembly as shown in the following diagram

CREATE ASSEMBLY MyLib FROM
'd:\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQL_SimpleCLR.dll'
WITH PERMISSION_SET = SAFE

5. Execute the following command to create function associated with the API provided in the class of the assembly (DLL)
CREATE FUNCTION dbo.DispTime
(
@result nvarchar(4000) = ''
)
RETURNS nvarchar(4000)
EXTERNAL NAME [MyLib].[UserDefinedFunctions].[DispTime]

6. Execute the following command to test the function:
sp_configure 'clr enabled', 1
go
reconfigure
go

SELECT dbo.DispTime('mypassword')