在 CTE 中设置一个变量 [英] SET a variable inside a CTE

问题描述

这是我第一次使用 SQLServer 2012，但我的代码不起作用:

This is my first time I work with SQLServer 2012, and I have this code that it doesn't work:

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.

推荐答案

您需要将 SET 替换为 SELECT 即可使用

You need to replace SET with SELECT and it will work

所以这个

SET @xml = (

需要替换为

SELECT @xml = (

这篇关于在 CTE 中设置一个变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！