How to move through a recordset in a Stored Procedure

[joebickley]

Hi
Im a newbie. In VB i am able to create a recordset and cycle through the records doing a set actions on each row. How can i achive this in a stored procedure.

Thanks

Joe Bickley

 

[tlbroadbent]

You can create a cursor and use a loop control (If, While) in T-SQL. However, you should try to accomplish the processing with relational sets rather than procedural code when working in a relational database. Often, processing done procedurally in VB can be converted to operate against sets in SQL with a little thouight. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[joebickley]

i know i can use a while or for loop but thats not what i want to do. i want to know how to create a recordset so that i can navigate through it using a while loop. As i said i have achived this in VB but i am trying to remove the load on our network by doing this server side.

Thanks

Joe

 

[tlbroadbent]

Did you read the first few words of my reply?

"You can create a cursor ..." You use cursors in T-SQL to do what you did in VB. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[joebickley]

to be honest mate the first 3 words of my post are "i am a newbie". I havent got a clue what u r talking about. What i need to understand is the t-sql version of this vb code.
[tt]
dim db as database
dim rec as recordset
set db = currentdb() ' asumes use in MS access
set rec = db.Openrecordset(Tablex)
'alternatives required for
rec.movenext
rec.movefirst
[tt]
i know it may be fundementally different in TSQL but i dont know TSQL so i was after a little starter for ten if u know what i mean

Thanks

Joe

 

[tlbroadbent]

Have you looked in SQL Books Online (BOL) or any other documenation? People in this forum can provide some help, pointers and links but we can't teach T-SQL.

Cursors are the T-SQL equivalent of the VB record set. Basic info about cursors can be found under the following topics in SQL BOL - "Cursors" and "DECLARE CURSOR." You can also find this info at the Microsoft website.

Cursors:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_07_5lpz.asp

DECLARE CURSOR:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-dz_31yq.asp

You can find more links to tutorials, websites and other resources in the FAQ area of this forum. Look at subject 10, "SQL Server Resources" on the FAQ list page. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[joebickley]

Hahaha i got it sorted

You off course were right i just had to boggle my mind for a while looking through the book online abut how cursors work. Once i had the fundementals sorted it was OK.

Guess i was just being lazy i suppose

Thanks Again

Joe