Example of SQL CLR in SQL Server by serverku

After writing some previous posts of replication and mirroring, today i am writing about SQL CLR (common language runtime) server user defined function. As per msdn it is a SQL user-defined function by adding a User-Defined Function to a SQL Server project. After successful deployment, the user-defined function can be called and executed. So let us implement it with one example.

1. Enable clr in SQL Server.

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

2.  This is a sample C# code which have a logic to show up data.

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void HelloWorld_CS(
        SqlDateTime ParaDateTime, SqlString ParaVarchar, SqlInt64 ParaInt)
    {

        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            
            conn.Open();

            SqlCommand command = new SqlCommand("SELECT @ParaVarchar + ' Hello World', @ParaInt + 1, dateadd(dd,1,@ParaDateTime)" , conn);
            command.Parameters.AddWithValue("@ParaVarchar", ParaVarchar );
            command.Parameters.AddWithValue("@ParaInt", ParaInt);
            command.Parameters.AddWithValue("@ParaDateTime", ParaDateTime);

            SqlContext.Pipe.ExecuteAndSend(command);

            conn.Close();
        }
    }
}

3. Register a DLL.

4. Create a assembly with registered DLL.

CREATE ASSEMBLY HelloWorldCLR 
FROM 'C:\SQLCLR\HelloWorldTest.dll'
WITH PERMISSION_SET=SAFE;
GO 

5. Create a stored procedure which calls an assembly.

CREATE PROCEDURE Proc_CLR_HelloWorld
(
 @DateTest DATETIME,
 @VarcharTest NVARCHAR(100),
 @IntTest BIGINT
)
AS 
EXTERNAL NAME
    HelloWorldCLR.StoredProcedures.HelloWorld_CS
GO 

6. Finally, run that stored procedure with parameters.

EXEC Proc_CLR_HelloWorld 
 @DateTest ='01/01/2014',
 @VarcharTest = 'Test',
 @IntTest = 1
GO

You might work with SQL CLR and I would like to share your inputs and ideas the way you are using SQL CLR. Hope you like this post.