Solved

Store Procedure - Loop through data

Posted on 2009-07-10
3
251 Views
Last Modified: 2012-08-14
I have a very simple question regarding the SQL Server store procedure syntax.

I have a query that returns a set of data:
select myfield from mytable.

I want to execute that query and loop through the data set.

I tried:

declare
@results varchar(10)
select @results=myfield
from mytable

It fails obviously, because I expect over 100s of rows.

Could you post an example hot to accept a data set and loop through it ?

Thanks,
0
Comment
Question by:marper
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 35 total points
ID: 24823377
declare @i int ,@results varchar(10)
DECLARE @Tab table ( i int identity , results varchar(10 ) )

INSERT Into @tab
select myfield
from mytable
SELECT @i = @@ROWCOUNT
WHILE @@ROWCOUNT  > 0
BEGIN
  SELECT @Result = result from @tab where i = @i
   --- do your stuff here

   set @i = @i -1
END

0
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 20 total points
ID: 24823447
You can use a cursor to loop through your record set as well:
Here is an example: http://www.mssqltips.com/tip.asp?tip=1599
Hope that helps.
P.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24824008
Never use a cursor use the table variable
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question