問題描述

我已經(jīng)在一個存儲過程中聲明了 6 個變量，我想將查詢結(jié)果(最多可以帶 6 條記錄)存儲到這些變量中的每一個中.我的查詢?nèi)缦滤?

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 個結(jié)果，我想將空值放入其余的 @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 插入帶有標(biāo)識列的表變量中.然后根據(jù)標(biāo)識列值將變量設(shè)置為等于表中的 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);

這篇關(guān)于將查詢結(jié)果存儲在變量中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！