I can't seem to figure out why this error is occurring?Error SQL80001: Incorrect syntax: 'CREATE FUNCTION' must be the only statement in the batch. Miscellaneous Files
IF NOT EXISTS(SELECT * FROM [dbo].[_EFMigrationsHistory] WHERE [MigrationId] = N'20200718013559_CreateCombineResourcesFuncForScheduler07172020')
BEGIN
drop function if exists [dbo].[GetCombinedResources]
END;
GO
IF NOT EXISTS(SELECT * FROM [dbo].[_EFMigrationsHistory] WHERE [MigrationId] = N'20200718013559_CreateCombineResourcesFuncForScheduler07172020')
BEGIN
// ERROR IS RIGHT HERE
Create FUNCTION dbo.GetCombinedResources
(@resourceTypeName VARCHAR(max),
@scheduleId INT,
@scheduleEventId INT)
RETURNS VARCHAR(max)
AS
BEGIN
declare @result varchar(500)
set @result = ''
I have not checked it into VSTS yet. I see this error when I complete:
PM> script-migration -i
Here is my migration:
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(@"drop function if exists [dbo].[GetCombinedResources]
");
migrationBuilder.Sql(@"
Create FUNCTION dbo.GetCombinedResources
(@resourceTypeName VARCHAR(max),
@scheduleId INT,
@scheduleEventId INT)
RETURNS VARCHAR(max)
AS
BEGIN
declare @result varchar(500)
set @result = ''
select @result = @result + query.Name + ', ' from
(
SELECT distinct Resources.Name FROM Events INNER JOIN Schedules ON Events.ScheduleId = ScheduleId and ScheduleEventId = @scheduleEventId
) query
if (@result is null or @result = '')
BEGIN
return null
END
return LEFT(@result, NULLIF(LEN(@result)-1,-1))
END
");
}
Participant
1038 Points
2817 Posts
EF Core: Issue with creating FUNCTION in Ef Core Migration
Jul 18, 2020 02:33 AM|tvb2727|LINK
I can't seem to figure out why this error is occurring?Error SQL80001: Incorrect syntax: 'CREATE FUNCTION' must be the only statement in the batch. Miscellaneous Files
I have not checked it into VSTS yet. I see this error when I complete:
PM> script-migration -i
Here is my migration: