'CREATE FUNCTION' must be the first statement in a query batch. Entity Framework Code First

Question

I have some inline SQL Scripts (functions and stored procedures) generated with Entity framework , Code first approach.

Update-Database -Script -SourceMigration:0

With the above command I get SQL Script file that I execute on test or production.

However I cannot run the generated script because of the following error:

'CREATE FUNCTION' must be the first statement in a  query batch. 

The script is generated as:

IF @CurrentMigration < '201410150019333_CreatefnGenerateRequestCode' BEGIN CREATE FUNCTION [dbo].[fnGenerateRequestCode] ( @userID varchar(max) )
RETURNS varchar(14)
as

How can I fix this?

Answer 1

You can avoid the

should be the first statement in a batch file

error without adding GO statements by putting the sql inside an EXEC command:

Sql(EXEC('BEGIN CREATE FUNCTION etc'))

Reference:

https://stackoverflow.com/a/20352867/150342

Answer 2

You have to generate your code and execute it as dynamic sql.

DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 
'
IF OBJECT_ID(''fn_Test'') IS NOT NULL DROP FUNCTION fn_Test
GO

CREATE FUNCTION fn_Test(@a INT)
RETURNS INT
BEGIN
    RETURN @a
END
'
IF 1 = 1
BEGIN
    EXEC(@Sql)
END