update with variable and stop at some predefined target

When using update with variables, it seem the variable is always evaluated first before any other filed.
Also it will not respect the condition using variable with the where clause. Put another way, no, you ca not use variable to dynamically control the data being updated. One workaround is to set condition inside set section.
For example. Try this:


USE [Test]
GO

/****** Object:  Table [dbo].[UpdateTest]    Script Date: 8/22/2012 10:02:10 AM ******/
if (exists (select 1 from sys.Tables where name='UpdateTest'))
    DROP TABLE [dbo].[UpdateTest]
GO

/****** Object:  Table [dbo].[UpdateTest]    Script Date: 8/22/2012 10:02:10 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UpdateTest](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [count] [int] NOT NULL,
    [value] [int] NOT NULL,
 CONSTRAINT [PK_UpdateTest] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


SET IDENTITY_INSERT [dbo].[UpdateTest] ON

GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (1, 5, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (2, 12, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (3, 20, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (4, 1, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (5, 5, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (6, 98, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (7, 98, 0)
GO
SET IDENTITY_INSERT [dbo].[UpdateTest] OFF
GO


declare @sum int, @TagetHitTimes int
set @sum=0
set @TagetHitTimes=0

--update [UpdateTest] set val1=0

--select * from [UpdateTest]

update [UpdateTest]
set  
    -- variable seems to be computed first
    -- only add sum when target is not achived
    -- stop adding when target is achived
    @sum=case when @sum>24 then @sum else @sum+[count] end
    -- used to track the first time target is achived
    ,@TagetHitTimes=@TagetHitTimes+(case when @sum>24 then 1 else 0 end)
    ,[value]=case when  @TagetHitTimes<=1 then [value]+@sum else 0 end

select * from [UpdateTest]