Is this possible to be done in SQL procedure?

What sql statement would be able to get values of a field if the records returned are more than one?

For instance, I know that if only one record is needed to be set in a variable, I could do this:

Code:

---

declare @var1 varchar(10)
select @var1 = [fieldname] from [tablename] where [conditions]

---

So @var1 will contain the data.

But how would I do it if I know there will be more than one record returned?

can u tell me more elaborately like y u want to use this in this way? any specification etc... i can help u better if i get some more idea of y u doing this way and wat exactly u want and y u want that data... we have many ways of doing same things..

There are a few ways to do it, I typically use a cursor and then loop one by one through the records returned. I'm changing your code to show what it looks like for 2+ variables just incase you need more than 1

Code:

---

DECLARE @var1 varchar(10), @var2 as varchar(5)

DECLARE curTemp CURSOR FORWARD_ONLY
FOR SELECT [fieldname], [fieldname2] from [tablename] where [conditions]

OPEN curTemp -- This gets it ready but doesn't actually load it

-- Below runs it, and puts the values into the variables of 1st record
FETCH NEXT FROM curTemp INTO @var1, @var2

WHILE @@FETCH_STATUS = 0 -- Begin our while loop, while there are more records that is
BEGIN 
    PRINT @var1 + ' | ' + @var2  -- Print the values of the current record
    FETCH NEXT FROM curTemp INTO @var1, @var2  -- Go to the next record, and set the values
END

CLOSE curTemp
DEALLOCATE curTemp

---

From the above we're using a cursor which is slow, we defined it as forward_only to try to speed it up (you can't go backwards with a forward_only), and we're looping record by record which is also slow. But, it gets the job done. Like yogesh12 is probably implying one typically tries to avoid cursors and knowing exactly what it is you want, we can likely show another way. But, cursors are sometimes the way to go.

Quote:

---

Originally Posted by Venus

There are a few ways to do it, I typically use a cursor and then loop one by one through the records returned. I'm changing your code to show what it looks like for 2+ variables just incase you need more than 1

Code:

---

DECLARE @var1 varchar(10), @var2 as varchar(5)

DECLARE curTemp CURSOR FORWARD_ONLY
FOR SELECT [fieldname], [fieldname2] from [tablename] where [conditions]

OPEN curTemp -- This gets it ready but doesn't actually load it

-- Below runs it, and puts the values into the variables of 1st record
FETCH NEXT FROM curTemp INTO @var1, @var2

WHILE @@FETCH_STATUS = 0 -- Begin our while loop, while there are more records that is
BEGIN 
    PRINT @var1 + ' | ' + @var2  -- Print the values of the current record
    FETCH NEXT FROM curTemp INTO @var1, @var2  -- Go to the next record, and set the values
END

CLOSE curTemp
DEALLOCATE curTemp

---

From the above we're using a cursor which is slow, we defined it as forward_only to try to speed it up (you can't go backwards with a forward_only), and we're looping record by record which is also slow. But, it gets the job done. Like yogesh12 is probably implying one typically tries to avoid cursors and knowing exactly what it is you want, we can likely show another way. But, cursors are sometimes the way to go.

---

Thanks. This looks like what I'm looking for. I'll give it a try :)

Quote:

---

Originally Posted by benmartin101

Thanks. This looks like what I'm looking for. I'll give it a try :)

---

Exactly Benmartin.:p