Fetch Next Record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Fetch Next Record

Author

Topic

Swifty
Starting Member

4 Posts

Posted - 2011-11-22 : 08:24:30

I'm trying to create a stored procedure which will update fields in a view via a system procedure.

The update process is working apart from the fact it is only updating one record from the view.

Any ideas are more than welcome, and thanks in advance

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





ALTER PROCEDURE [dbo].[renew_subscribers]

AS

SET NOCOUNT ON

SET DATEFORMAT DMY


Declare
@RC int,
@Account varchar(10),
@Customer varchar(10),
@Primary varchar(10),
@CurrStart varchar(3),
@CurrEnd varchar(3),
@BatchISSUE varchar(3),
@BatchType varchar(3),
@Sublength varchar(1),
@NewStart varchar(3),
@NewEnd varchar(3)

Begin Transaction

Select
@Customer = CUCODE,
@Primary = CU_primary_2,
@CurrStart = CU_USRCHAR1,
@CurrEnd = CU_USRCHAR2,
@BatchISSUE = Substring(Od_detail,4,3),
@BatchType = Batch,
@Sublength = right(od_detail,1)

From vw_update_start_end

if @BatchType = 'REN' Set @NewStart = @CurrStart
if @BatchType = 'REN' Set @NewEnd = (@BatchISSUE) + (12*(@sublength)-1)
if @BatchType = 'ARC' Set @NewStart = @batchIssue
if @BatchType = 'ARC' Set @NewEnd = (@batchIssue) + (12*(@sublength)-1)
if @BatchType = 'NEW' set @NewStart = @batchIssue
if @BatchType = 'NEW' set @NewEnd = (@batchIssue) + (12*(@sublength)-1)

DECLARE Order_Cursor CURSOR FOR

SELECT CUCODE

FROM vw_update_start_end

OPEN Order_Cursor

FETCH NEXT FROM Order_Cursor Into @ACCOUNT

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC @RC = [SYSTEM_STORED_PROCEDURE]

@PS_Insert_Flag = 0,
@PS_Account = @Customer,
@PS_User_ID = 'SA',
@PS_User_Char1 = @NewStart,
@PS_User_Char2 = @NewEnd,
@PS_User2 = 'SUBSCRIBER',
@PS_Account_primary = @Primary


FETCH NEXT FROM Order_Cursor Into @ACCOUNT

END

CLOSE Order_Cursor

DEALLOCATE Order_Cursor

Commit Transaction

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-22 : 08:42:39

How many times is the stored procedure called?
Could be the view that's at fault or the stored procedure isn't updating what it is meant to - or something else.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Swifty
Starting Member

4 Posts

Posted - 2011-11-22 : 08:55:25

The system stored procedure should be getting called for each customer that needs updating. But it seems to be only updating the last record in the view! The view looks fine(!)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-22 : 12:38:34

The SELECT at the top of the stored procedure is only pulling one record. I suspect that your UPDATE is dependent on those values selected which would explain why only one row is getting changed.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-22 : 13:00:55

Ah yes, the cursor just gets the CUCODE. For each of these the SP is called with the variables set from the select from the view (which sounds like it ends up with the "last" row in the table) and that is updated multiple times.

Get rid of the select and add all those variables andcolumns to the cursor probably

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Swifty
Starting Member

4 Posts

Posted - 2011-11-22 : 13:06:25

My eyes have gone square, I can't see it!

Thanks for the reply Bustaz Kool, I'll take a look in the morning when I'm fresh(!)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-22 : 13:09:34

Select
@Customer = CUCODE,
@Primary = CU_primary_2,
@CurrStart = CU_USRCHAR1,
@CurrEnd = CU_USRCHAR2,
@BatchISSUE = Substring(Od_detail,4,3),
@BatchType = Batch,
@Sublength = right(od_detail,1)

From vw_update_start_end

Sets the variables to a value. This is outside the loop so the values are constant for each call of the SP. The only thing that changes is @ACCOUNT.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Swifty
Starting Member

4 Posts

Posted - 2011-11-22 : 13:17:25

Thanks for the reply I was giving it once last go and wrote it like this, and it appears to work

DECLARE Update_Cursor CURSOR FOR

SELECT CUCODE, CU_primary_2, CU_USRCHAR1, CU_USRCHAR2, Substring(Od_detail,4,3), Batch, right(od_detail,1)

FROM vw_update_start_end

OPEN Update_Cursor

FETCH NEXT FROM Update_Cursor Into @Customer, @Primary, @CurrStart, @CurrEnd, @BatchISSUE, @BatchType, @Sublength

while @@fetch_status = 0

BEGIN

if @BatchType = 'REN' Set @NewStart = @CurrStart
if @BatchType = 'REN' Set @NewEnd = (@BatchISSUE) + (12*(@sublength)-1)
if @BatchType = 'ARC' Set @NewStart = @batchIssue
if @BatchType = 'ARC' Set @NewEnd = (@batchIssue) + (12*(@sublength)-1)
if @BatchType = 'NEW' set @NewStart = @batchIssue
if @BatchType = 'NEW' set @NewEnd = (@batchIssue) + (12*(@sublength)-1)

EXEC @RC = [SYSTEM_STORED_PROCEDURE]

@PS_Insert_Flag = 0,
@PS_Account = @Customer,
@PS_User_ID = 'SA',
@PS_User_Char1 = @NewStart,
@PS_User_Char2 = @NewEnd,
@PS_User2 = 'SUBSCRIBER',
@PS_Account_primary = @Primary

FETCH NEXT FROM Update_Cursor Into @Customer, @Primary, @CurrStart, @CurrEnd, @BatchISSUE, @BatchType, @Sublength

END

CLOSE Update_Cursor

DEALLOCATE Update_Cursor

Commit Transaction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources