How to deploy a managed stored procedure without using Visual Studio?

Question

All I read says that when creating a managed stored procedure, right-click in Visual Studio and select Deployment. This works great, but what if I want to deploy it outside of Visual Studio in several different places? I tried to create an assembly with a DLL project built in SQL, and although he added the assembly, he did not create procedures from the assembly. Has anyone figured out how to do this directly in SQL without using Visual Studio?

Answer 1

Copy the DLL assembly file to a local drive on different servers. Then register your assembly in the database:

create assembly [YOUR_ASSEMBLY] from '(PATH_TO_DLL)' 

... then you create a function that references the corresponding public method in the DLL:

 create proc [YOUR_FUNCTION] as external name [YOUR_ASSEMBLY].[NAME_SPACE].[YOUR_METHOD] 

Be sure to use [brackets, especially around NAME_SPACE. Namespaces can have any number of dots in them, but SQL identifiers cannot, unless the parts are explicitly separated by square brackets. This was the source of many headaches when I first used the SQL CLR.

To be clear, [YOUR_ASSEMBLY] is the name you defined in SQL; [NAME_SPACE] is the .NET namespace inside the DLL where you can find your method; and [YOUR_METHOD] is just the name of the method in this namespace.

Answer 2

To add a more detailed / explanation to @kcrumley anwser above:

[NAME_SPACE] is the full name , not just the namespace
- i.e. if your class is called StoredProcedures in the StoredProcedures namespace, you should use [My.Name.Space.StoredProcedures] for the [NAME_SPACE] part.

If managed stored procedures reside in a class without a specific namespace, you simply use the name of the bare class (for example, [StoredProcedures] ).

I also tried to work a bit on how to add a procedure with arguments / parameters. So this is a sample for those who are still trying to do this:

 CREATE PROCEDURE [YOUR_FUNCTION] ( @parameter1 int, @parameter2 nvarchar ) WITH EXECUTE AS CALLER AS EXTERNAL NAME [YOUR_ASSEMBLY].[StoredProcedures].[YOUR_FUNCTION]