CLR Stored Procedures: how to set the schema/owner?

Question

Hi there, I am working on a Linq based CLR Stored Procedure for some complex filtering and manipulation, which would otherwise require a lot of messy and poorly performant T-SQL code, if implemented in a more "traditional" Stored Procedure.

This is working great, but I can't find how to set the schema of this Stored Procedure in phase of deployment, for a better organization and separation of the database objects in modules.

Any ideas?

Many thanks in advance.

Answer 1

When you create the procedure referencing the assembly you can create this wrapper being owned by any schema you want. See This MSDN article on deploying CLR stored procedures for a walkthrough of how to do deploy a stored procedure. By changing the create procedure statement to something like:

CREATE SCHEMA foo

CREATE PROCEDURE foo.hello
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld

You can now have a procedure owned by the foo schema.