'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?

score 4 · Answer 1 · edited May 23 '17 at 12:02

4

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

edited May 23 '17 at 12:02

Community

1
1

answered Nov 03 '14 at 11:03

Colin

21,807
16
99
186

score 2 · Accepted Answer · answered Nov 03 '14 at 06:27

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

answered Nov 03 '14 at 06:27

SubqueryCrunch

1,183
10
17

Thank you! End up going to the path that I did not want to at first place. Still better than that error. – codebased Nov 07 '14 at 00:09
@elle0087. Yes it is as long as you embed the sql file in your assembly. – codebased Dec 18 '17 at 00:16

score 0 · Answer 3 · answered Nov 17 '20 at 21:10

You need to execute the previous statements, to the CREATE FUNCTION, on a first EXEC statement and then run the CREATE FUNCTION into another EXEC statement.

DECLARE @query NVARCHAR(3000) = ''

SET @query += 'SET ANSI_NULLS ON' + CHAR(10) 

SET @query += 'SET QUOTED_IDENTIFIER ON' + CHAR(10) 

SET @query +=  'IF EXISTS (SELECT *
                           FROM   sys.objects
                           WHERE  object_id = OBJECT_ID(N''[dbo].[' + @DB_Name + '_InitCap]'')
                                  AND type IN ( N''FN'', N''IF'', N''TF'', N''FS'', N''FT'' ))
                  DROP FUNCTION [dbo].['+ @DB_Name + '_InitCap]' + CHAR(10)

EXEC sp_executesql @query

SET @query = 'CREATE FUNCTION [dbo].[' + @DB_Name + '_InitCap] ( @InputString varchar(4000) ) RETURNS VARCHAR(4000) AS' + CHAR(10)
....
....
EXEC sp_executesql @query

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

3 Answers3