Microsoft – SQL Server – Transact SQL – Cursor – Number of Records

Background

One of the more difficult things to get good education on in the Database World is cursors.

In Oracle, quote a bit of things are dealt with through Cursors; especially in PL/SQL.

But, then when one is with the Microsoft crowd, there is quite a bit of pissing on Cursors.

 

Introduction

I am reviewing a bakers dozen set of Scripts from our App Vendor and they use Cursors to process records seeded in a few tables. And, I am hoping not to be the next Justin Bieber looking for my Run Away Love ( Run away Love –  https://www.youtube.com/watch?v=ZGdZqV7k0qM )

In my case, Runaway Cursor…

 

 

How many records in Cursor?

How many records are in our cursor; in essence how many records should we expect to process?

Well, MSFT, as always, has that covered, just use @@CURSOR_ROWS

 

Sample Code

 DECLARE @DBName sysname
 DECLARE @iRecordNumber bigint
 DECLARE @strRecordID char(7)
 DECLARE @iRecordIDLength int
 DECLARE @iNumberofRecordsInCursor bigint
 DECLARE @bDebug bit

 DECLARE @strLog nvarchar(600)

 DECLARE @CHAR_TAB varchar(30)
 DECLARE @CHAR_CRLF varchar(30)

 set @bDebug =1
 set @CHAR_TAB = char(9)
 set @CHAR_CRLF = char(13) + char(10)

 set @iRecordNumber = 0
 set @strRecordID = ''
 set @iRecordIDLength = datalength( @strRecordID)

 DECLARE @cursorDB CURSOR

 if (@bDebug =1)
 begin

	set @strLog =
		          @CHAR_TAB
			+ ' List of Databases'


	print @strLog

 end


 /******************************************************************************
  Declare Cursor
	as SELECT tblDB.name from sys.databases tblDB
 *******************************************************************************/
  SET @cursorDB = CURSOR FOR

	 SELECT tblDB.name
	 from   sys.databases tblDB
			  
 

 /******************************************************************************
  Open Cursor
 *******************************************************************************/
 OPEN @cursorDB


 /******************************************************************************
  Get Number of Records in Cursor
 *******************************************************************************/
 set @iNumberofRecordsInCursor = @@CURSOR_ROWS


 if (@bDebug =1)
 begin

	set @strLog =
			  @CHAR_TAB
			+ ' Expected Number of record(s) in cursor : '
			+ cast(@iNumberofRecordsInCursor as sysname)

	print @strLog

 end

 FETCH NEXT
 FROM  @cursorDB
 INTO  @DBName
		

  if (@bDebug =1)
  begin

	set @strLog =
		  @CHAR_TAB
		+ '_________________________________________________________'


	print @strLog

  end
 

 WHILE @@FETCH_STATUS = 0

 BEGIN

	set @iRecordNumber= @iRecordNumber+ 1


	if (@bDebug =1)
	begin

		set @strLog =
				  @CHAR_TAB
				+ replicate(' ', @iRecordIDLength - len(@iRecordNumber))
				+ cast(@iRecordNumber as sysname)
				+ ' of '
			        + cast(@iNumberofRecordsInCursor as sysname)
				+ ') '
				+ @DBName

		print @strLog

	end

	FETCH NEXT
	FROM  @cursorDB
	INTO  @DBName


 END

 /******************************************************************************
  Close Cursor
 *******************************************************************************/
 
 CLOSE @cursorDB

 /******************************************************************************
  Deallocate Cursor
 *******************************************************************************/
 DEALLOCATE @cursorDB

 print ''
 print ''


  if (@bDebug =1)
  begin

	set @strLog =
		         @CHAR_TAB
			+ '_________________________________________________________'


	print @strLog

  end

 if (@bDebug =1)
 begin

	set @strLog =
			  @CHAR_TAB
			+ ' Number of record(s) in cursor : '
			+ cast(@iRecordNumber as sysname)

	print @strLog

 end


  if (@bDebug =1)
  begin

	set @strLog =
		       @CHAR_TAB
		      + '_________________________________________________________'


	print @strLog

  end
go

 

Summary

Yes, quite of trepidation with Cursors traced back to a strong preference for things to be handled in groups\subsets; declaratively so to speak, as opposed to imperatively or procedurally.

But, once one jumps into programming idioms such as PL/SQL or Transact SQL, one likely knows what one is trying to do.

 

 

Reference

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s