[dba-SQLServer] Advancing in SQL Server

Paul,

Yes, it is possible.
You will need to look up CURSOR in the BOL.
A cursor is the same as a recordset that you can scroll through forward.


Robert

At 12:00 PM 2/22/2007, you wrote:
>Date: Thu, 22 Feb 2007 13:06:40 +0100 (CET)
>From: paul.hartland at fsmail.net
>Subject: [dba-SQLServer] Advancing in SQL Server
>To: accessd <accessd at databaseadvisors.com>,     SQL Group
>         <dba-sqlserver at databaseadvisors.com>
>Message-ID:
>         <12660593.1538681172146000895.JavaMail.www at wwinf3004.me-wanadoo.net>
>Content-Type: text/plain; charset=UTF-8
>
>To all,
>
>I know how to write basic stored procedures etc, but I want to know 
>if the following is possible.  I have the code below in Visual Basic:
>Dim rsOnJob As ADODB.Recordset
>                 Dim rsNew As ADODB.Recordset
>                 Dim rsDupe As ADODB.Recordset
>
>                 Set rsOnJob = New ADODB.Recordset
>                 rsOnJob.CursorLocation = adUseClient
>                 rsOnJob.Open ("SELECT PayrollNo FROM 
> tblAvailabilityAll WHERE JobNo = '" & PublicJobNumber & "'"), 
> DESQLGenesis.SQLConn, adOpenDynamic, adLockReadOnly
>
>                 If (rsOnJob.RecordCount > 0) Then
>                     With rsOnJob
>                         .MoveFirst
>                         Do Until (.EOF)
>                             Set rsNew = 
> DESQLGenesis.SQLConn.Execute("genesis_select_NewStarterNotification 
> '" & .Fields("PayrollNo") & "'")
>                             If (rsNew.RecordCount > 0) Then
>                                 Set rsDupe = 
> DESQLGenesis.SQLConn.Execute("genesis_select_DupeInNewStarter '" & 
> rsNew.Fields("PayrollNo") & "'")
>                                 If (rsDupe.RecordCount < 1) Then
>                                     DESQLGenesis.SQLConn.Execute 
> ("genesis_insert_NewStarters '" & rsNew.Fields("PayrollNo") & "', 
> '" & rsNew.Fields("Fullname") & "', '" & 
> Format(rsNew.Fields("StartDate"), "MM/DD/YY") & "', '" & 
> rsNew.Fields("JN") & "', '" & Format(rsNew.Fields("JD"), "MM/DD/YY") & "'")
>                                 End If
>                                 rsDupe.Close
>                                 Set rsDupe = Nothing
>                             End If
>
>                             rsNew.Close
>                             Set rsNew = Nothing
>
>                             .MoveNext
>                         Loop
>                     End With
>                 End If
>
>                 rsOnJob.Close
>                 Set rsOnJob = Nothing
>
>Does anyone know if it is possible to put this into either a stored 
>procedure or a user defined function enabling me to just make a call 
>to the one stored procedure of function ?
>
>Thanks in advance for any help on this.
>
>
>
>
>
>Paul Hartland
>paul.hartland at fsmail.net
>07730 523179