?
Solved

Interate thru a sql select in stored proc

Posted on 2009-12-21
7
Medium Priority
?
338 Views
Last Modified: 2012-05-08
Hi,
I nead some help looping thru a select statement. I have a store proc that accepts a an id as a paramater. I have a select statement that returns 10 ids, is there a way for me to iterate thru each id and call the sp for each iteration?

thanks
0
Comment
Question by:CraigLazar
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 15

Expert Comment

by:rajeeshmca
ID: 26102382
the only wau u could iterate is using the cursor
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 26102386
So you want to iterate from .NET or from SQL Server?, you can do it in both places.

In .NET saving your table in a dataTable and then iterate through the rows of your dataTable object.

In SQL you can iterate with a cursor.
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 26102413
hi, i want to use it in the sql query window.
so do u have a sample of iterating using a cursor?
 
thanks
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 15

Expert Comment

by:jorge_toriz
ID: 26102432
Like this?
DECLARE @Name NVARCHAR(50)
DECLARE products_cursor FOR
SELECT Name
FROM Production.Product

OPEN products_cursor

FETCH NEXT FROM products_cursor
INTO @Name

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @Name

	FETCH NEXT FROM products_cursor
	INTO @Name
END

CLOSE products_cursor
DEALLOCATE products_cursor

Open in new window

0
 
LVL 15

Expert Comment

by:rajeeshmca
ID: 26102439
yes.. that is the way of doing an iterate in sql
0
 
LVL 11

Accepted Solution

by:
Goodangel Matope earned 2000 total points
ID: 26102697
Lets assume your stored procedure is called spProcessRecord, and it accepts a parameter of type int.

This is how you would do it
DECLARE @RecordID INT

DECLARE IDList CURSOR FOR
SELECT [ID] from tblID -- whatever query is returning the ids

OPEN IDList -- runs your select statement and opens the result set

FETCH NEXT FROM IDList into @RecordID -- gets the first record in the result set

WHILE @@FETCH_STATUS = 0 -- checks to see if there are more records to process
BEGIN

  EXEC spProcessRecord @RecordID; -- executing your stored procedure 

  FETCH NEXT FROM IDList into @RecordID; -- gets the net record from the result set

END

CLOSE IDList
DEALLOCATE IDList

Open in new window

0
 
LVL 4

Author Closing Comment

by:CraigLazar
ID: 31668858
Perfect thanks so much for he example of code...

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

839 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