Solved

how to browse through recordset in stored procedure

Posted on 2013-06-22
2
309 Views
Last Modified: 2013-07-17
Hi,

I want to write one stored procedure with select statement and i want to traverse through each record as we do in programming using while look. How can i do that.

i.e. say i want to execute sql statement SELECT * FROM <TABLE NAME> and then i want to browse through resultant recordset, one by one record in stored procedure. how can i do that.
0
Comment
Question by:ronipats
2 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39268878
you have to use CURSORs in the stored procedure

the below link might help
http://msdn.microsoft.com/en-us/library/ms180169.aspx
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 39269000
There is no need for a cursor
Suppose your table has primary key ID and you want to go through the field FirstName

declare @ID int
declare @FirstName varchar(15)

set @ID = 0
while EXISTS(select top 1 1 from Table where ID >= @ID)
begin
  select top 1 @ID = ID, @FirstName = FirstName
  from Table
  where ID >= @ID

  set @ID = @ID + 1

  --do stuff with your variable @FirstName
end

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now