CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2012sql-function
Matt
Matt

Reputation: 27011

Creating a function in a stored procedure

I have a stored procedure that updates a couple aggregates and a function - by dropping them, the assembly and re-importing them.

My code:

CREATE PROCEDURE [maint].[UpdateSqlClr]
AS
     BEGIN

    IF OBJECT_ID('dbo.[HistoricAnalysis]') IS NOT NULL
    DROP FUNCTION [dbo].[HistoricAnalysis];
IF OBJECT_ID('dbo.[OverallStatus]') IS NOT NULL
    DROP AGGREGATE [dbo].[OverallStatus];
IF OBJECT_ID('dbo.[OverallStatusBreakdown]') IS NOT NULL
    DROP AGGREGATE [dbo].[OverallStatusBreakdown];
IF EXISTS
(
    SELECT *
    FROM sys.assemblies
    WHERE [name] = 'SuperDbSqlCLR'
)
    DROP ASSEMBLY [SuperDbSqlCLR];

CREATE ASSEMBLY [SuperDbSqlCLR] FROM 'd:\Super\SuperDb.Sql.CLR\SuperDb.SqlClr.dll' WITH PERMISSION_SET = SAFE;


CREATE AGGREGATE [dbo].[OverallStatus]
(@input [NVARCHAR](4000)
)
RETURNS [NVARCHAR](4000)
EXTERNAL NAME
     SuperDbSqlCLR.[SuperDb.SqlClr.OverallStatus];
CREATE AGGREGATE [dbo].[OverallStatusBreakdown]
(@input [NVARCHAR](4000)
)
RETURNS [NVARCHAR](4000)
EXTERNAL NAME
     [SuperDbSqlCLR].[SuperDb.SqlClr.OverallStatusBreakdown];

-- Where the GO would be in a query window

CREATE FUNCTION [dbo].[HistoricAnalysis] ()
RETURNS TABLE (
  [Sequence] [int] NULL,
  [Text] [nvarchar](100) NULL
)
AS
EXTERNAL NAME [SuperDbSqlCLR].[SuperDb.SqlClr.UserDefinedFunctions].[HistoricAnalysis]

    END

The problem is that while I can add a GO when just running the contents of the SPROC in a query window, SPROC creations don't seem to like the GO statement, and every time, I get a syntax error that 'CREATE FUNCTION' must be the only statement in the batch --

Is there anyway to do this in a SPROC?

Upvotes: 0

Views: 169

Answers (1)

Daniel A. Thompson
Daniel A. Thompson

Reputation: 1924

This should work using the exec command, i.e. dynamic SQL.

Upvotes: 2

Related Questions