How do I deploy a managed stored procedure without using Visual Studio?

Question

Everything I have read says that when making a managed stored procedure, to right click in Visual Studio and choose deploy. That works fine, but what if I want to deploy it outside of Visual Studio to a number of different locations? I tried creating the assembly with the dll the project built in SQL, and while it did add the assembly, it did not create the procedures out of the assembly. Has anyone figured out how to do this in SQL directly, without using Visual Studio?

Answer 1

Copy your assembly DLL file to the local drive on your various servers. Then register your assembly with the database:

create assembly [YOUR_ASSEMBLY]
from '(PATH_TO_DLL)'

...then you create a function referencing the appropriate public method in the DLL:

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

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

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

Answer 2

For add some more detail/clarification to @kcrumley's anwser above:

[NAME_SPACE] is the fully qualified type name and not just the namespace
- i.e. if your class is called StoredProcedures in a namespace of My.Name.Space, you must use [My.Name.Space.StoredProcedures] for the [NAME_SPACE] part.

If your managed stored procedures are in a class without a namespace defined, you just use the bare class name (e.g. [StoredProcedures]).

I also struggled for a bit trying to work out how to add a procedure with arguments/parameters. So heres a sample for anyone else trying to do so:

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