You’ve Generated a CLR Procedure, Now What?

SQL.CLR makes it easy to generate CLR stored procedures and user defined functions. Just select then object and click go! One nice feature is that you can select the options to create a project and/or compile an assembly for deploying to SQL Server. This offers 2 ways to deploy the CLR object.

First, by creating a SQL Server project, the code generated from the existing T-SQL object can be modified to add, edit or remove any custom business logic. This is where CLR really provides us with tremendous flexibility when designing our logic on the data. We have the entire .NET base class library, not to mention any custom libraries we have developed, to leverage in our project. Once we have made the changes to our code, we can simply right-click our project and select “Deploy.”


The second option is to have SQL.CLR compile an assembly from the existing object. This means that we will not modify any existing logic in the code. From here we need to open SSMS and run a few lines of T-SQL to continue the deployment. Create an assembly in the database you wish to store the code for the object.

CREATE ASSEMBLY [Assembly_Name] FROM '\\[Path_to_assembly]\[Assembly_Name].dll' WITH PERMISSION_SET = SAFE

Next create the object, a stored procedure in this example with the format of:

CREATE PROCEDURE [Procedure_Name] AS EXTERNAL NAME [AssemblyName].[Namespace.ClassName].[MethodName]

Now that we have deployed our assembly, created the stored procedure all that’s left is to execute the stored procedure…

EXEC [Procedure_Name]