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

Community · Accepted Answer · 2017-05-23 12:02:32Z

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, 2017 at 12:02

CommunityBot

11 silver badge

answered Nov 3, 2014 at 11:03

Colin

22.7k17 gold badges111 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SubqueryCrunch · Accepted Answer · 2014-11-03 06:27:21Z

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 3, 2014 at 6:27

SubqueryCrunch

1,50713 silver badges17 bronze badges

2 Comments

codebased Over a year ago

Thank you! End up going to the path that I did not want to at first place. Still better than that error.

codebased Over a year ago

@elle0087. Yes it is as long as you embed the sql file in your assembly.

Mauricio Gracia Gutierrez · Accepted Answer · 2020-11-17 21:10:12Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related