从SQL查询存储多个值到存储过程变量

时间:2022-06-23 18:06:21

I want to do something like the example below

我想做一些类似下面的例子

ColumnAVal = Select ColumnA from TableA where....
ColumnBVal = Select ColumnB from TableA where....
Select * from TableB where ColumnC = (value from ColumnA)
Select * from TableC where ColumnD = (value from ColumnB)

I have to get the values from TableA query which has one hefty where clause. And I have to get about 20 different columns from TableA. So, above example won't be a good idea. I am trying to find a better way to save these values, that doesn't require me to use same where clause 20 times.

我必须从TableA查询中获取值它有一个大where子句。我需要从TableA得到20个不同的列。上面的例子不是个好主意。我正在寻找一个更好的方法来保存这些值,这并不要求我使用相同的where子句20次。

Basically idea is to get the 20 or so columns from the TableA and save those as variables for later use. That way I can create just one query to save the column values instead of calling it twenty times.

基本的想法是,从TableA中获取20个左右的列,并将这些列保存为以后使用的变量。这样,我可以创建一个查询来保存列值,而不是调用它20次。

E.g.

如。

@QuantityAvailable =  SELECT TOP 1 WLPS_Qty_Available FROM 

TBL_Warehouse_Location_Parts_Spec, TBL_Location_Master, 
TBL_Warehouse_Master  WHERE     WLPS_Parts_Spec_ID = @PartSpecID AND    WLPS_Part_Type_ID IN ( 0, @PartTypeID ) AND WLPS_Active_Flag = 'Y' AND ( WLPS_Location_ID = @LocationID )  

I have to run the same query again and again 20 times. And I would prefer I don't have to, to save some processing.

我必须反复运行同一个查询20次。我宁愿我不需要,保存一些处理。

2 个解决方案

#1


3  

declare @ColumnAVal varchar(20);
declare @ColumnBVal varchar(20);

select @ColumnAVal = ColumnA , @ColumnBVal = ColumnB from TableA where....

The values used are from the last row in the returned set so it only makes sense when the select returns a single row or it's suitably ordered (less good).

所使用的值来自返回集中的最后一行,因此只有当select返回单个行或它有适当的排序(不太好)时才有意义。

#2


0  

--I have to assume that the where clause is different in each case

我必须假设where子句在每种情况下都是不同的

Select * from TableB where ColumnC IN (Select ColumnA from TableA where....)
Select * from TableC where ColumnD IN (Select ColumnB from TableA where....)

#1


3  

declare @ColumnAVal varchar(20);
declare @ColumnBVal varchar(20);

select @ColumnAVal = ColumnA , @ColumnBVal = ColumnB from TableA where....

The values used are from the last row in the returned set so it only makes sense when the select returns a single row or it's suitably ordered (less good).

所使用的值来自返回集中的最后一行,因此只有当select返回单个行或它有适当的排序(不太好)时才有意义。

#2


0  

--I have to assume that the where clause is different in each case

我必须假设where子句在每种情况下都是不同的

Select * from TableB where ColumnC IN (Select ColumnA from TableA where....)
Select * from TableC where ColumnD IN (Select ColumnB from TableA where....)