在SQL Server 2016中使用本机汇编的Alter表值函数错误

Question

我有以下脚本来改变我的本机编译函数。我希望它返回表。我还将单个语句更改为多语句。所以我将returns更改为begin atomic。

ALTER FUNCTION [dbo].[MovieSimularity]
(   
    @movieID int
)
RETURNS @result Table (movieID int, distance int)
WITH Native_Compilation, SCHEMABINDING
as
Begin ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
--begin
    declare @m int
    declare @countZero int
    declare @r Table (movieID int, distance int)
    declare cur cursor local for 
        select movieID
        from dbo.Movies
        where movieID != @movieID and dbo.CompareMovieHashes(@movieID, movieID) = 1
    open cur
    fetch next from cur into @m
    while @@FETCH_STATUS = 0
    begin
        insert into @r 
            select @m, dbo.MovieEuclideanDistance(@movieID,@m) as distance
        set @countZero = (select count(*) from @r where distance = 0)
        if(@countZero > 5)
            break
        fetch next from cur into @m
    end
    close cur
    deallocate cur
    return
        select top(5) *
        from @r
        order by distance   
end

执行给我以下错误：

Msg 487, Level 16, State 1, Procedure MovieSimularity, Line 12 [Batch Start Line 7]
An invalid option was specified for the statement "CREATE/ALTER FUNCTION".
Msg 319, Level 15, State 1, Procedure MovieSimularity, Line 13 [Batch Start Line 7]
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Procedure MovieSimularity, Line 44 [Batch Start Line 7]
Incorrect syntax near 'end'.

我不知道有什么问题。请帮助

Answer 1

开始原子是ANSI SQL标准的一部分。SQL Server支持 原子块在本质上汇编的存储过程， 以及本质上的编译，标量用户定义的功能。为了 有关这些功能的更多信息，请参阅标量用户定义 内存中OLTP的功能

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/atomic-blocks-ind-native-procedures

原子能值不受表值函数(根据文档(的支持，仅是标量值函数。

此查询有什么问题？为什么需要光标？

DECLARE @movieID INT = 1;
SELECT TOP(5)
      movieID
    , dbo.MovieEuclideanDistance(@movieID,m.movieID) AS distance
FROM
    dbo.Movies
WHERE
    movieID != @movieID
    AND dbo.CompareMovieHashes(@movieID, movieID) = 1
ORDER BY
    distance

这是解决您的0距离问题的解决方案，而无需使用光标或循环。根据您的需求进行调整。

DECLARE
    @rowsToCalculate INT = 5
  , @currentX DECIMAL(8,5)
  , @currentY DECIMAL(8,5)
;
-- Get the current movie's X and Y coordinates
SELECT
  , @currentX = CoordX
  , @currentY = CoordY
FROM
  dbo.Movies
WHERE
  MovieID = @movieID
;
-- First get the list with distance 0 (x and y coordinates are equal)
INSERT INTO @result (MovieID int, Distance int)
SELECT TOP(@rowsToCalculate)
    MovieID
  , 0 AS Distance
WHERE
  CoordX = @currentX
  AND CoordY = @currentY
  AND MovieID != @movieID
  AND dbo.CompareMovieHashes(@movieID, MovieID) = 1
;
-- Figure out how many records do we need and get them if there is any.
SET @rowsToCalculate = @rowsToCalculate - (SELECT COUNT(*) FROM @result);
IF (@rowsToCalculate > 0) BEGIN
  INSERT INTO @result (MovieID int, Distance int)
  SELECT TOP(@rowsToCalculate)
      movieID
    , dbo.MovieEuclideanDistance(@movieID,m.movieID) AS distance
  FROM
    dbo.Movies
  WHERE
    movieID != @movieID
    AND dbo.CompareMovieHashes(@movieID, MovieID) = 1
    AND (CoordX != @currentX OR CoordY != @currentY)
  ORDER BY
    Distance
END