在CTE中设置变量

Question

这是我第一次使用SQLServer 2012，并且我收到的这段代码不起作用：

CREATE PROCEDURE ReadCodeBuffer
    @gint bigint,   
    @pres tinyint,
    @level bigint,
    @quantity smallint,
    @xmlGenerated xml OUTPUT
AS

    DECLARE @current_incremental bigint
    DECLARE @counter bigint
    DECLARE @xml XML

    -- Get current incremental.
    set @current_incremental = 
        (SELECT INCREMENTAL
            FROM INCREMENTAL_TABLE
            WHERE GTIN = @gint AND
              PRESENTATION = @pres AND
              LEVEL = @level)

    -- 
    with Numbers as
    (
      select row_number() over(order by 1/0) as N
      from sys.all_objects as o1 cross join
           sys.all_objects as o2
    )
    SET @xml = (
    select @gint as GINT,
           @pres as PRESENTATION,
           @level as LEVEL,
           N + @current_incremental as INCREMENTAL
    from Numbers
    where N < @quantity
    for xml path('row'), root('root'), type)

    SET @xmlGenerated = @xml
GO

这段代码有问题：

SET @xml = (
    select @gint as GINT,
           @pres as PRESENTATION,
           @level as LEVEL,
           N + @current_incremental as INCREMENTAL
    from Numbers
    where N < @quantity
    for xml path('row'), root('root'), type)

我有以下三个错误：

Incorrect syntax near SET.
Column name N not valid.
Object name 'Numbers' not valid.

Answer 1

您需要替换SET为SELECT，它将起作用

所以这

SET @xml = (

需要替换为

SELECT @xml = (