[dba-SQLServer] Advancing in SQL Server

Paul:  Yes, it looks to me like you need a cursor.  You can run the sql
statements within the cursors, or you can call sp's.  Below is some
quickie sample code:



DECLARE CurB CURSOR FOR
	SELECT DISTINCT m.fldDate, m.Brand, m.Type
	FROM  tblRptLiaDaily M
	WHERE m.packageid is null

	DECLARE @BlDate datetime
	DECLARE @BlBrand char(3)
	DECLARE @BlType char(1)
	
OPEN CurB

FETCH NEXT FROM CurB INTO @BlDate, @BlBrand, @BlType
WHILE @@FETCH_STATUS=0
BEGIN

	EXEC usp_xyz @BlDate=@BlDate, @BlBrand=@BlBrand, at BlType=@BlType
	--OR do any number of other things.

	FETCH NEXT FROM CurB INTO @BlDate, @BlBrand, @BlType
END

CLOSE CurB
DEALLOCATE CurB


----------------------------------------------------------------------

Message: 1
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

------------------------------

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver


End of dba-SQLServer Digest, Vol 48, Issue 18
*********************************************