How can I step through a table.one by one

[rtdvoip]

Hiya fellas,

Is there a way to go through a table step by step.
I need to grab a pin number and a duration.
Then using those two identifiers I need to access other
objects in seperate tables to compute values and return the values to the row associated with the pin and duration I just picked out. Then go to the next one down the line until the EOF.

Thanks in advance,
rtdvoip

 

[dmhirsch]

Sounds like you're used to using recordsets in VB. The equivalent in SQL is cursors. There's a lot of syntax, so start with books online. BUT, there's probably a better way (given what you've described) to write a single SQL update statement that would do the trick, and much faster than cursors.

 

[SQLSister]

You can do this but it's a bad idea from a performance standpoint. You should be able to do this using a computed column or function and then do the whole set of records in one step.

 

[LogicalmanUS]

rtdvoip,

You can use either a Cursor, Hash table or, possibly, a derived table (SubSelect query) within a SProc to complete this task.
I use the latter two as, unfortunately, our DBA has a hatred of Cursors.
If you post the relevent table/column names, and computation required, I will take a look at it this evening and reply by tomorrow.

Logicalman

 

[SQLSister]

Logical, your dba has a hatred of cursors for good reason. They kill performance on the database.

 

[rtdvoip]

LogicalmanUS

Here is what I have put together so far. It is stepping through but now I need to do some other computations.
I suppose I need to do these computations whhile in the while @@FETCH NEXT = 0. Any suggestions?

/* Begin code for Fetch */
USE phase1billing
GO
Declare @pin varchar(28)
Declare @duration int
DECLARE clarent_cursor CURSOR FOR

SELECT Clarent_pin, duration FROM b_t_cp2

ORDER BY Clarent_pin

OPEN clarent_cursor

-- Perform the first fetch.
FETCH NEXT FROM clarent_cursor
INTO @pin, @duration


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM clarent_cursor
INTO @pin, @duration

END

CLOSE clarent_cursor
DEALLOCATE clarent_cursor
GO

/* end code for Fetch */

So I also need to update other column fields while having the @pin and @duration variables


thanks
rtdvoip

 

[rtdvoip]

SQLSister,

Thanks for the info on performance. I just need something to show on Monday. It is not on a live system but just an example of how the database will function.

rtdvoip

 

[rtdvoip]

Everything was steping through fine but now that I am trying to

          update traffic
[code]          set duration=@duration
[code]          where CURRENT OF clarent_cursor

I am getting the following error...

Server: Msg 16929, Level 16, State 1, Line 51
The cursor is READ ONLY.
The statement has been terminated.

any suggestions?

rtdvoip

 

[dmhirsch]

An example from BOL:

USE Northwind
GO
DECLARE abc CURSOR FOR
SELECT CompanyName
FROM Shippers
OPEN abc
GO
FETCH NEXT FROM abc
GO
UPDATE Shippers SET CompanyName = N'Speedy Express, Inc.'
WHERE CURRENT OF abc
GO
CLOSE abc
DEALLOCATE abc
GO

 

[rtdvoip]

dmhirsch

It is almost exactly what I did...check this out.

GO
Declare @pin varchar(128)
Declare @duration int,@r_r_indx int,@billing_factor int
declare @duration_factor int, @duration_factor_comp int


DECLARE clarent_cursor CURSOR FOR

SELECT Clarent_pin, duration FROM b_t_cp2

ORDER BY Clarent_pin

OPEN clarent_cursor

-- Perform the first fetch.
FETCH NEXT FROM clarent_cursor
INTO @pin, @duration

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
update b_t_cp2
set duration_factor_compute = @duration_factor_comp
Where Current of clarent_cursor

FETCH NEXT FROM clarent_cursor
INTO @pin, @duration


END

CLOSE clarent_cursor
DEALLOCATE clarent_cursor
GO

 

[dmhirsch]

Hmmmm.... Try a FOR UPDATE after the ORDER BY

 

[LogicalmanUS]

rtdvoip,

SQLSister, Yep, I know that's reason, but it seems, from many posts on here, that so many programmers still prefer to use such things (must have 8/16 proc servers!!)

The way around using cursors, if it is not possible to use a truly derived table, is to use a hash table, and make it act like a cursor.

The basic layout for this is thus:

CREATE TABLE #myTable
(
myID int,
myText varchar(100),
myNewText varchar(100),
myFlag int
)

INSERT #myTable
SELECT ID, RTRIM(LTRIM(TEXT)), '', 0 FROM tblSOMETABLE

DECLARE @MyID int
DECLARE @NewText varchar(100)
SET @MyID = (SELECT TOP 1 myID FROM #myTable WHERE FLAG = 0)
WHILE NOT(@MyID IS NULL)
BEGIN
SET @NewText = (SELECT aText FROM tblANOTHERTABLE WHERE ID = @MyID )
UPDATE #myTable
SET myNewText = @NewText
FLAG = 1
WHERE MyID = @MyID
SET @MyID = (SELECT TOP 1 myID FROM #myTable WHERE FLAG = 0)
END

DROP TABLE #myTable

(Before someone says something, this was just a bit of fun code for demo)
As you can see, rather than using the FETCH statement and testing for null, you simply select the top record from the hash table, do whatever you need to with the data, and then set that records flag. Then move onto the next one just like another FETCH.

Simple, effective, easy to understand (and debug), and uses up a lot less CPU time (without crashing other DBs) and keeps my DBA happy with me!

Logicalman

 

[rtdvoip]

LogicalmanUs

I just about have it working.
now all i need to do is revisit my string manipulation problem from yesterday. It was not what I was thinking.
It works ok if you set the @variables but I need to get
the call_price from the rate_plan table by using the rate_plan_index that I have and the comparison of the @breakout_no[that i also have] to the prefix in the rate_plan table.

declare @i int, @x int, @call_price int, @rate_plan_index int
declare @breakout_no char(10)
set @rate_plan_index = 1
set @breakout_no = '1999999999'
set @i = 1
set @x = len (@breakout_no)
print @x

set @call_price = (select call_price
from rate_plan
where rate_plan_index = @rate_plan_index
and prefix = (select (while @i < len (breakout_no)
begin
substring(@breakout_no,1,@x)
set @i=@i+1
set @x=@x-1
end)))

I know this will spit out errors but it is just the start of developing something that will work.

I will revist the thread on this from yesterday to see if there is anything there that will help.

thanks.

 

[rtdvoip]

Good Monday,

AS I have shown the database program and the computing is correct, I greatly realize SQLSister's reply. I am running my program against 5000 records and it is taking 3 minutes.
As I was going to now try a hash table, I am wondering if I can.

If I create a hash table with Pin (only unique identifier), duration, bil_type, Breakout_number...
I need to step though this for a comparison to a rate_plan table where the breakout_number is compared to a string found in the rate_plan table. when a match is found, i will need to update the billing_traffic table.

My question is this:
I can step though the #mytble but when I need to update the billing_traffic table will not my update select every row where the pin from #mytbl is = to the thousand in the Billing_traffic?

Any ideas on how to get around this situation?

thanks,
rtdvoip

 

[LogicalmanUS]

rtdvoip,

Do you have a PK/FK relationship between the rate_plan and billing_traffic tables? If so, and it refers to only those records in the billing_traffic table (was is the breakout_number?), then you can use the update statement on the billing_traffic table with the breakout_number in the where statement.

Does that make sense? Or have I got the columns/tables totally screwed up?

Logicalman