How can I return a sql select into a sql variable

Question

Hi,

I'm trying to put the results of a SELECT into a variable and loop through the results to manipulate that data, all in the same stored proceedure... Here's what I have so far:

DECLARE @i int

@Result = (SELECT * FROM UserImport)

SET @i = 0
WHILE @i < (SELECT Count(@Result) As Count)
BEGIN
   /* Do Stuff */
END

I know I'm way off because it's saying @Result was not declared, but I'm not sure how to declare a variable to be able to hold the results of a SELECT statement.

Can anyone tell me where i'm going wrong and how to fix it?

Thanks,
Matt

Answer 1

Consider using table variables instead of cursors

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=529

Of course, you should always favor set based operations over any of these. SQL is designed for set based operations.

Answer 2

You can use a cursor, at least that's the traditional way of doing it. You can also use a while loop as you requested. See this article for an example of a cursor and the alternative.

Avoiding SQL cursors

Answer 3

Rather than thinking in terms of looping over the results, look hard at the "Do stuff" part, see if you can do it all as one step without looping or cursors.

Post more details on the actual work you need to do if you can, it's likely a cursor is not needed.

Answer 4

looping is one of the worst way to kill database performance!

I would suggest you attempt to process the select statement in a single command, something like this:

UPDATE t2
    SET Cola=t1.col1, Colb=t1.col2
    FROM UserInput t1
    JOIN YourTable t2 ON t1.id=t2.id

however if you must loop do it this way:

DECLARE @Current int, @LastRow int
DECLARE @Col1 datatype, @Col2 datatype ....
DECLARE @Results  table (RowId int identity1,1) primary key, col1 ...,col2 ...)

INSERT INTO @Results SELECT * FROM UserImport
SELECT @Current=0, @LastRow=@@ROWCOUNT

WHILE @Current<@LastRow
BEGIN
    SET @Current=@Current+1
    SELECT @Col1=Col1, @Col2=col2 FROM @Results WHERE RowId=@Current

   /* Do Stuff */

END

if you are processing more than 100 rows, replace the table variable: @Results with a temp table: #Results, like:

CREATE TABLE #RESULTS (RowId int identity1,1) primary key, col1 ...,col2 ...)

because that will be faster.