[dba-SQLServer] Advancing in SQL Server

paul.hartland at fsmail.net paul.hartland at fsmail.net
Thu Feb 22 06:06:40 CST 2007


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


More information about the dba-SQLServer mailing list