[dba-SQLServer] Advancing in SQL Server

Cursors are really slow and you should generally avoid them. If for some
reason you need to use cursors they need to be FAST FORWARD cursors.

I use a variation of this code fragment to get around cursors. You basicaly
create a temp table (in the example below it's a table variable instead of a
temp table) and insert the primary key of the table you want to process. You
then determine how many records you processed and then you loop thru those
records and run your main sql process inside of that loop.

/*Prepare TABLE variable to take resultset
it's important to declare an identity field since we will use this
as part of our criteria later on */

DECLARE @tbl TABLE
(	
          tbl_RowID INT IDENTITY(1, 1),
          tbl_ObjectID INT
)

/*Local variables */
DECLARE @ObjectID	INT,
	@count int, /*create local @@fetch_status*/
	@iRow int   /*row pointer (index)*/

/* create array simulator */
INSERT @tbl (tbl_ObjectID)
/* get the recordset and stuff the values into the array */
SELECT ObjectID
FROM MySQLTable
/*get array Upper Bound (highest ID number)*/
SET @count = @@ROWCOUNT

/*initialize index counter*/
SET @iRow = 1

/*establish loop structure*/
WHILE @iRow <= @count
	BEGIN
		/*get row values*/
		SELECT @ObjectID = tbl_ObjectID
		FROM @tbl
		WHERE tbl_RowID = @iRow
		
		/* INSERT MAIN SQL CODE HERE */
		

		/*uncomment line below for debugging*/
		--PRINT 'Retreiving record #' + RTRIM(CAST(@iRow as char))

		/*go to next row*/
		SET @iRow = @iRow + 1
	END
 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David Lewis
Sent: Thursday, February 22, 2007 10:33 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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
*********************************************
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.441 / Virus Database: 268.18.3/697 - Release Date: 2/22/2007
11:55 AM