Is this possible to be done in SQL procedure?

[benmartin101]

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:

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?

[yogesh12]

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

[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

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.

[benmartin101]

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

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

[yogesh12]

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

Exactly Benmartin.