Before we start this article, I have listed a few related post which you would like to check out…
- How to create a CLR Stored Procedure using C# and Visual Studio
- How to pass parameters to CLR Stored Procedures using C#
- How to return data records from a CLR Stored Procedure
There are many reasons why you would manually want to deploy a CLR stored procedure to SQL Server Management Studio. There are two parts to deploying any CLR Stored Procedure
- Part I : Deploying Assembly
- Part II : Deploying CLR Stored Procedure
1. Deploying Assembly
Once your CLR stored procedure is ready, build the project(Shift + F6). You should get the following in your output window(Ctrl + W, O).
Compile complete -- 0 errors, 1 warnings
CLR Stored Procedures -> C:YasserMyClrDemobinDebugMyClrDemo.dll
This is the path where your assembly is located, use this path to CREATE ASSEMBLY as follows, remember to set the PERMISSION_SET to 'SAFE'
CREATE ASSEMBLY HelloWorldAssembly from 'C:YasserMyClrDemobinDebugMyClrDemo.dll'
WITH PERMISSION_SET = SAFE
2. Deploying CLR Stored Procedure
Now that we have our assembly created(HelloWorldAssembly), next step is to create a stored procedure which uses this assembly.
Now consider we have the following CLR Stored procedure
public class HelloWorldClass
public static void HelloWorldMethod()
So keeping the above classname, stored proc name and assembly name in mind below is how your 'create procedure' should be like.
CREATE PROCEDURE hello
EXTERNAL NAME HelloWorldAssembly.HelloWorldClass.HelloWorldMethod
Hope this helps :)
Further Reading :