sql - 在 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 = (