Solved

how to browse through recordset in stored procedure

Posted on 2013-06-22
2
320 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:Ephraim Wangoya
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

680 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