Storing multiple values from SQL query to stored procedure variable

Question

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.

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.

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.

I'm not quite following. Tables don't have where clauses. Also, tag your question with the database you are using . . . sample data and desired results are helpful. And what does columnB have to do with anything? — Gordon Linoff
– Gordon Linoff, Commented Mar 24, 2016 at 3:15

LoztInSpace · Accepted Answer · 2016-03-24 03:27:47Z

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).

edited Mar 24, 2016 at 3:27

answered Mar 24, 2016 at 3:25

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Vincent Over a year ago

What happens if there is more then one result?

LoztInSpace Over a year ago

You will get the last one based upon whatever order the set that comes back in. With ORDER BY you can make it deterministic, without it you cannot be sure which one you'll get. TOP 1 with ORDER BY makes your intent clear and hopefully is accurate.

Vincent Over a year ago

That's clearly not how the question is posed. "Basically idea is to get the 20 or so columns from the TableA"

jitendragarg Over a year ago

20 or so columns, not 20 or so records. Record count is always going to be one. Otherwise, I can't do without a table, anyway. :)

LoztInSpace Over a year ago

@Vincent Yes I understand the question well enough (I apparently have answered it correctly) but I am having trouble understanding yours. What is the meaning of "more than one result" in this context if not rows?

|

Vincent · Accepted Answer · 2016-03-24 03:31:20Z

0

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

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

answered Mar 24, 2016 at 3:31

Vincent

8827 silver badges13 bronze badges

Collectives™ on Stack Overflow

Storing multiple values from SQL query to stored procedure variable

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related