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

sql-server stored-procedures sql-server-2005 sqlclr sql-server-data-tools
Vito Botta picture Vito Botta · May 13, 2009 · Viewed 7k times · Source

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

bopapa_1979 picture bopapa_1979 · Jan 4, 2011

UPDATE: In Visual Studio 2012 this can now be accomplished via the project properties window of a "SQL Server Database Project." The relevant property is "Default Schema" on the "Project Settings" tab. Modifying this value modifies the generated deployment script to put the Schema name in front of Functions, Stored Procedures, Etc... Be sure to add a Schema object to your project with the same name or you will get build errors.

I don't know what version of Visual Studio you are using, but when you create a CLR stored procedure project in Visual Studio 2010, the project includes two SQL scripts: PreDeploymentScript.sql and PostDeploymentScript.sql.

We just use these to maniuplate things the way we want.

In the pre-deployment script, we have something like this:

-- DROP EXISTING ITEM FROM CURRENT SCHEMA
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Utilities].[fn_Create_Md5_Hash]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [Utilities].[fn_Create_Md5_Hash]
GO

Then, in the post-deployment script, we have this:

-- DEPLOYMENT WIZARD RECREATES ITEM IN dbo SCHEMA
-- DROP NEW ITEM FROM dbo SCHEMA
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Create_Md5_Hash]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_Create_Md5_Hash]
GO

-- RECREATE THE ITEM BACK IN THE SCHEMA YOU WANT
CREATE FUNCTION [Utilities].[fn_Create_Md5_Hash](@source [varbinary](max))
RETURNS [varbinary](8000) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [NameSpace].[UserDefinedFunctions].[fn_Create_Md5_Hash]
GO

Hope that helps!

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More
Check if a string contains a substring in SQL Server 2005, using a stored procedure

I've a string, @mainString = 'CATCH ME IF YOU CAN'. I want to check whether the word ME is inside @mainString. How do I check if a string has a specific substring in SQL?

Read More