How can I return a sql select into a sql variable

Question

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

Can you expand on what you want to happen in /* Do Stuff */? There may be a better solution. — BradBrening
– BradBrening, Commented May 3, 2010 at 0:37

mdma · Accepted Answer · 2010-05-02 23:30:42Z

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

answered May 2, 2010 at 23:30

mdma

57.9k12 gold badges97 silver badges128 bronze badges

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

Comments

SqlACID · Accepted Answer · 2010-05-02 23:57:28Z

1

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.

answered May 2, 2010 at 23:57

SqlACID

4,02423 silver badges29 bronze badges

Comments

Raj Kaimal · Accepted Answer · 2010-05-03 04:39:48Z

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.

edited May 3, 2010 at 4:39

answered May 2, 2010 at 23:28

Raj Kaimal

8,31429 silver badges19 bronze badges

1 Comment

anonymous user Over a year ago

cursors are the worst way to loop, google sql server cursor free looping, there are many article like this: searchsqlserver.techtarget.com/tip/…

anonymous user · Accepted Answer · 2010-05-03 04:16:30Z

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.

Collectives™ on Stack Overflow

How can I return a sql select into a sql variable

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related