将查询结果存储在变量中

Store query result in variable(将查询结果存储在变量中)
本文介绍了将查询结果存储在变量中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

限时送ChatGPT账号..

我已经在一个存储过程中声明了 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);

这篇关于将查询结果存储在变量中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持html5模板网!

【网站声明】本站部分内容来源于互联网,旨在帮助大家更快的解决问题,如果有图片或者内容侵犯了您的权益,请联系我们删除处理,感谢您的支持!

相关文档推荐

What SQL Server Datatype Should I Use To Store A Byte[](我应该使用什么 SQL Server 数据类型来存储字节 [])
Interpreting type codes in sys.objects in SQL Server(解释 SQL Server 中 sys.objects 中的类型代码)
Typeorm .loadRelationCountAndMap returns zeros(Typeorm .loadRelationCountAndMap 返回零)
MS SQL: Should ISDATE() Return quot;1quot; when Cannot Cast as Date?(MS SQL:ISDATE() 是否应该返回“1?什么时候不能投射为日期?)
Converting the name of a day to its integer representation(将一天的名称转换为其整数表示)
How to convert nvarchar m/d/yy to mm/dd/yyyy in SQL Server?(如何在 SQL Server 中将 nvarchar m/d/yy 转换为 mm/dd/yyyy?)