looping a query

Can anyone give me a hand on how to loop this query until there are no results left, this is going to be part of a stored procedure by the way.

Code Snippet

declare @.strcharid varchar(21)

set @.strcharid = (select top 1 struserid from userdata where class = '108' and authority = '1')

update tb_user set strauthority = '255', BanReason = 'Master Skill Hack', TermDate = getdate() where straccountid in (select top 1 straccountid from ip_tracker where strcharid = @.strcharid)
update userdata set loyalty = '0' where struserid = @.strcharid

Ken:

Are you using SQL Server 2000 or SQL Server 2005?

|||sql 2000 now, the programs i need to run do not work with 2005 unfortuantely.
|||

My first pass at this might be something like this:

Code Snippet

update tb_user
set strauthority = '255',
BanReason = 'Master Skill Hack',
TermDate = current_timestamp
from tb_user a
join ( select p.strUserId,
( select top 1 q.strAccountId
from ip_tracker q
where p.strUserId = q.strCharId
)
from userData p
where p.class = '108'
and p.authority = '1'
) b
on a.strAccountId = b.strAccountId

update userdata
set loyalty = '0'
where class = '108'
and authority = '1'

I don't feel real good about this solution; will somebody please check me?

|||it returns the error "Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'b'."

my current thought is this, i just need a way to make it rerun until @.count = 0

Code Snippet

CREATE PROCEDURE [MasterHack]
as

begin tran
declare @.strcharid varchar(21)
set @.strcharid = (select top 1 struserid from userdata where class = '108' and authority = '1')
declare @.count int
set @.count = (select count(1) struserid from userdata where class = '108' and authority = '1')

if @.count > 0
begin

update tb_user set strauthority = '255', BanReason = 'Master Skill Hack', TermDate = getdate() where straccountid in (select top 1 straccountid from ip_tracker where strcharid = @.strcharid)
update userdata set loyalty = '0' where struserid = @.strcharid
end

rollback tran
GO

|||

Thank you, Ken.

Change this:

Code Snippet

join ( select p.strUserId,
( select top 1 q.strAccountId
from ip_tracker q
where p.strUserId = q.strCharId
)
from userData p
where p.class = '108'
and p.authority = '1'
) b

to this:

Code Snippet

join ( select p.strUserId,
( select top 1 q.strAccountId
from ip_tracker q
where p.strUserId = q.strCharId
) as strAccountId
from userData p
where p.class = '108'
and p.authority = '1'
) b

Also, I understand that in your original post that this is not the style of code that you sought; however, Transact SQL is in general a set-based language and works far better if you devise set-based processes rather than record-based processes. My response is an attempt to steer your solution to a set-based solution rather than a record-based solution.

Also, if you are going to be writing stored procedures or functions it is good to get into the habbit of writing set-based or set-oriented solutions rather than record-oriented solutions. Set-oriented / set-based solitions are the preferred choice frequently discussed in the MSDN Transact SQL forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

Hopefully, other memembers will come forwared with other forum recommendations.

|||Thank you, is there any site you would suggest for this style?
sql