July 28, 2008 at 7:07 am
Hi,
In a function I am writing, I need to read a number of values (in this example two) from a table and store them into variables. What the best way to do this?
DECLARE @a INT
DECALRE @b-2 INT
SET @a = (SELECT colA FROM mytable)
SET @b-2 = (SELECT colB FROM mytable)
==OR==
DECLARE @a INT
DECLARE @b-2 INT
DECLARE mycur CURSOR FOR
SELECT colA,colB FROM mytable
OPEN mycur
FETCH NEXT FROM mycur INTO @a,@b
CLOSE mycur
DEALLOCATE mycur
In the real world I have six (sometimes more) variables.
Thanks,
Ray
July 28, 2008 at 7:14 am
It depends on what you want to do. In your first option you are only going to get the column from the first row returned (could vary based on order by). If you are limiting your results to 1 row returned then something similar to your first option is the way to do it. You can assign values to variables like this:
[font="Courier New"]SELECT
@var1 = col1,
@var2 = col2,
...
FROM
table1
WHERE
[criteria to return 1 row]
[/font]
If you need to loop through multiple rows, changing the values in the variables then you would need to use a loop or cursor like in your second example.
Without knowing what your entire process is designed to do it is hard to give an exact answer. Best practices are to avoid cursors/loops whenever possible and use set-based methods to accomplish your desired outcome.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 28, 2008 at 7:18 am
Super! Your example (values from just one row) was right what I needed.
Thanks!
Ray
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply