How to identify a field in a cursor 3

[LonnieJohnson]

I am new to curosrs and have one that contains three fields. I wnat to use one of the fields to do something to each row.

How do I identify just one field in a curor? Here is my code.

DECLARE CURSOR1 CURSOR
FOR select CSNBR, LNAME, FNAME from vw_DischargesForClosedFiles
OPEN CURSOR1
FETCH CURSOR1

DECLARE @MSG AS VARCHAR(100)

While @@Fetch_Status =0
BEGIN

--I want to use the CSNBR as a variable in some other code to add a record to another table.

Fetch Next from CURSOR1
End

CLOSE CURSOR1
DEALLOCATE CURSOR1

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[bborissov]

DECLARE @CSNBR (put the type of CSNBR field here)

DECLARE CURSOR1 CURSOR
FOR select CSNBR, LNAME, FNAME from vw_DischargesForClosedFiles
OPEN CURSOR1

FETCH CURSOR1 TO @CSNBR

DECLARE @MSG AS VARCHAR(100)

While @@Fetch_Status =0
BEGIN
-- Use @CSNBR 

Fetch Next from CURSOR1 TO @CSNBR
End

CLOSE CURSOR1
DEALLOCATE CURSOR1

CURSOR!!! Keep in mind that they are speed killers. Can't you do this with set based commands?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[memdiscount]

You are missing this part

FETCH NEXT FROM CURSOR1
INTO @variable01, @variables02...

You need to declare variables for each field to save each value into those variables.

Just select those values which you want to use in a loop and define variables for each field.

See this example for the Cursor, how to use variables to store values of the cursors fields.

*********************************************

SET NOCOUNT ON

DECLARE @vendor_id int, @vendor_name nvarchar(50),
@message varchar(80), @product nvarchar(50)

PRINT '-------- Vendor Products Report --------'

DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From Vendor: ' +
@vendor_name

PRINT @message

-- Get the next vendor.
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor

*************************************

Thanks

 

[RiverGuy]

You use variables, for example:

Fetch Next from CURSOR1 INTO @CSNBR, @LNAME, @FNAME.

But try to do you process without using a cursor at all. It's best to avoid cursors in SQL Server whenever you can.

 

[SQLSister]

I would never consider doing an insert through a cursor. do this instead

insert mytable (CSNBR, LNAME, FNAME)
select CSNBR, LNAME, FNAME from vw_DischargesForClosedFiles

The code is simpler to maintain and willbe much much faster!




"NOTHING is more important in a database than integrity." ESquared

 

[LonnieJohnson]

Thanks everyone.

I actually used an insert query but was experimenting with CURSORS. I wanted to learn how to use them.

I notice that everyone always says don't use them. I guess I would ask why do they exist if no one of any experience uses them? When would you use them?

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[JoeAtWork]

>>>> When would you use them?

As a last resort.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[SQLSister]

They are available for a few things that are difficult to do otherwise. One place where they are used is when you want to send emails to a group of people that are personalized for each person. Since you wouldn't want the to address to contain the emails of everyone you are sending and the personalization would be differnt, it can be best to create a proc that builds the email and have a cursor send the information one record at a time to build and send each email. Another place is if you want to run system stored procs with a variety of inputs. Probably the majority of cursors that need to be run are run by system administrators doing admin tasks. Almost nothing done by a user application should use a cursor.

In any case the experienced developer would only run a cursor if he or she could see no other choice, if the number of records to loop through was relatively small and often only during the nonbusy hours of the server.

Cursors are extremely slow, I've seen set-based replacements go from hours to minutes or minutes to milliseconds. The reason why you would never want to use one in an insert, update, or delete is that you could potentially lock up the table for hours (especially if someone was so inexperienced as to put one in a trigger).

"NOTHING is more important in a database than integrity." ESquared

 

[LonnieJohnson]

Thanks. I needed that information and will keep it in mind for the future SQLSister.

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[gmmastros]

Another good use for cursors is calculating a running total. There are set based solution for this, but they perform worse than a cursor. How much worse? Well... I was curious, so I threw this together.

The general idea here is...

Create a test table with an identity column and a random number column (both integers).

Then, run three different methods and compare the execution time. To make things fair, each method returns the result as a single recordset.

[green]-- Create a TestRunningTotal Table[/green]
Create Table TestRunningTotal (Num Int Identity(1,1) Primary Key, RandomNumber Int)

Declare @i Int
Set @i = 0

While @i < 10000
	Begin
		Insert Into TestRunningTotal Default Values
		Set @i = @i + 1
	End

[green]-- Set the random number [/green]
Declare @Temp Table (Id Int Identity(1,1), RandomNumber Int)

Insert Into @Temp(RandomNumber) 
Select Num
From   TestRunningTotal
Order By NewId()

Update TestRunningTotal 
Set    RandomNumber = A.RandomNumber
From   TestRunningTotal
       Inner Join @Temp A
         On TestRunningTotal.Num = A.Id

Declare @Start DateTime
Set @Start = GetDate()

[green]-- Beginning of cursor method.[/green]

Declare @Num Int, @Value Int, @RunningTotal BigInt
Declare @Output Table (Num Int, RandomValue Int, RunningTotal Int)

Declare MyCursor CURSOR FOR
Select  Num, RandomNumber
From    TestRunningTotal
Order By Num

Open	MyCursor

Fetch Next From MyCursor Into @Num, @Value

Set @RunningTotal = @Value
While @@Fetch_Status = 0
  Begin
	Insert Into @Output Values(@Num, @Value, @RunningTotal)
    Fetch Next From MyCursor Into @Num, @Value
	Set @RunningTotal = @RunningTotal + @Value
  End
Insert Into @Output Values(@Num, @Value, @RunningTotal)

Close MyCursor
Deallocate MyCursor
Select * From @Output

Select 'Cursor', DateDiff(Millisecond, @Start, GetDate())

[green]-- beginning of sub-query method[/green]

Set @Start = GetDate()
Select Num, RandomNumber, (Select Sum(RandomNumber) From TestRunningTotal Where Num <= A.Num) As [Running Total]
From   TestRunningTotal As A
Order By A.Num

Select 'Sub-Query Method', DateDiff(Millisecond, @Start, GetDate())

Set @Start = GetDate()

[green]-- beginning of join method.[/green]

Select A.Num, A.RandomNumber, Sum(B.RandomNumber) As [Running Total]
From   TestRunningTotal As A
       Inner Join TestRunningTotal As B
			On B.Num <= A.Num
Group By A.Num, A.RandomNumber
Order By A.Num

Select 'Cross Join Method', DateDiff(Millisecond, @Start, GetDate())

Drop Table TestRunningTotal

The cursor method executes in 0.5 seconds.
The sub-query method takes 12 seconds.
The join method takes 4.5 seconds.

As you can see, the cursor method is many times faster for running this type of query. I'm not surprised at the difference in time between the sub-query and join methods. In my opinion, sub-queries are usually slower than join. What is surprising is that the cursor method is faster.

In my opinion, the most important aspect of SQL Code is to maintain the integrity of your data. Second most important is execution time. In this rare circumstance, cursors are faster than set-based queries. As far as I know (other than DBA type jobs), this is the only exception to the rule, "Don't use cursors".



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

Now that is something I had not seen before. Thanks for the additional info George.

"NOTHING is more important in a database than integrity." ESquared