Solved

Store Procedure - Loop through data

Posted on 2009-07-10
3
254 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

713 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