将查询结果存储在变量中

2021-09-16 00:00:00 variables sql-server stored-procedures

我已经在一个存储过程中声明了 6 个变量，我想将查询结果(最多可以带 6 条记录)存储到这些变量中的每一个中.我的查询如下所示:

I have declared 6 variables in a stored procedure and I'd like to store a query result (which may bring up to 6 records) into each one of those variables. My query looks like this:

  DECLARE
    @Sib1 varchar(20),
    @Sib2 varchar(20),
    @Sib3 varchar(20),
    @Sib4 varchar(20),
    @Sib5 varchar(20),
    @Sib6 varchar(20)

select
    PC.SKU
from
    Product PC
    where
        Parent_code in (select
                                  Parent_code
                              from
                                  Product 
                              where
                                  SKU =12345)
        and ParentFlag <> 'p'
        and SKU <> 12345
order by Parent_Child_Priority desc

我想将每个生成的 SKU 放入每个 @SIB 变量中.如果它只返回 1 个结果，我想将空值放入其余的 @SIB 变量中.

I'd like to put each one of the resulting SKU in each @SIB variables. if it only returns 1 result, I'd like to put null values into the rest of the @SIB variables.

谢谢.

推荐答案

您可以将 SKU 插入带有标识列的表变量中.然后根据标识列值将变量设置为等于表中的 sku.

You could insert the SKU's into a table variable, with an identity column. Then set the variables equal to the sku in the table based on the identity columns value.

DECLARE @Sib1 VARCHAR(20)
    ,@Sib2 VARCHAR(20)
    ,@Sib3 VARCHAR(20)
    ,@Sib4 VARCHAR(20)
    ,@Sib5 VARCHAR(20)
    ,@Sib6 VARCHAR(20);

DECLARE @TempTbl TABLE (
    RowID INT IDENTITY
    ,SKU VARCHAR(20)
    )

INSERT INTO @TempTbl (SKU)
select
    PC.SKU
from
    Product PC
    where
        Parent_code in (select
                                  Parent_code
                              from
                                  Product 
                              where
                                  SKU =12345)
        and ParentFlag <> 'p'
        and SKU <> 12345
order by Parent_Child_Priority desc

SELECT @Sib1 = SKU
FROM @TempTbl
WHERE RowID = 1;

SELECT @Sib2 = SKU
FROM @TempTbl
WHERE RowID = 2;

SELECT @Sib3 = SKU
FROM @TempTbl
WHERE RowID = 3;

SELECT @Sib4 = SKU
FROM @TempTbl
WHERE RowID = 4;

SELECT @Sib5 = SKU
FROM @TempTbl
WHERE RowID = 5;

SELECT @Sib6 = SKU
FROM @TempTbl
WHERE RowID = 6;

编辑

DECLARE @SQL VARCHAR(MAX);

SET @SQL = 'SELECT SKU, ..., sum(convert(INT, a.qty)) AS ' + @sib1 + ' FROM ...'
EXEC (@SQL);

相关文章