stored procedure cursor update problem

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)

stored procedure cursor update problem

Author

Topic

yns.emre
Starting Member

11 Posts

Posted - 2009-10-19 : 04:18:32

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spControl]
as
begin

declare @Memberkey int,@MemberState int

declare Member_cursor cursor for

SELECT DISTINCT MemberKey from Member where MemberSta=619
and MemberKey<>-1

open Member_cursor

fetch next from Member_cursor into @Memberkey

while @@Fetch_Status = 0
begin

set @MemberState=1

UPDATE Member_LOG SET MemberTip=@MemberState where MemberKey=@Memberkey

fetch next from Member_cursor into @Memberkey

end

close Member_cursor
deallocate Member_cursor

end

the code does not update Member_LOG table,how can i fix it ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-19 : 05:58:07

UPDATE L
SET L.MemberTip=1
from Member_LOG as L inner join MemberKey as M on L.MemberKey=M.Memberkey
where M.MemberSta=619 and MemberKey<>-1

Madhivanan

Failing to plan is Planning to fail

yns.emre
Starting Member

11 Posts

Posted - 2009-10-19 : 06:04:42

i want it in a cursor not direct an update query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-19 : 06:13:20

quote:
Originally posted by yns.emre

i want it in a cursor not direct an update query

Why do you want to use a cursor?

Madhivanan

Failing to plan is Planning to fail

yns.emre
Starting Member

11 Posts

Posted - 2009-10-19 : 06:26:40

instead of "set @MemberState=1 " there is a select query with subquery se @memberstate=SELECT Key .... ,
why dou you ask like this,i said the code above is not working if u have a solution to make it working u can say,i want to make it(the above code) working understand ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-19 : 06:31:04

quote:
Originally posted by yns.emre

instead of "set @MemberState=1 " there is a select query with subquery se @memberstate=SELECT Key .... ,
why dou you ask like this,i said the code above is not working if u have a solution to make it working u can say,i want to make it(the above code) working understand ?

Then post the full code

Madhivanan

Failing to plan is Planning to fail

yns.emre
Starting Member

11 Posts

Posted - 2009-10-19 : 06:48:08

set @MemberState=1

it sets memberstate to 1 but after that it does not execute the code below

UPDATE Member_LOG SET MemberTip=@MemberState where MemberKey=@Memberkey

in visual studio i trace it, although the parameters (memberstate and memberkey) have true values,update code does not work,the problem is this

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-19 : 06:55:59

How many rows do you get for this?

SELECT DISTINCT MemberKey from Member where MemberSta=619
and MemberKey<>-1

Madhivanan

Failing to plan is Planning to fail

yns.emre
Starting Member

11 Posts

Posted - 2009-10-19 : 07:09:17

170 rows

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources