October 27, 2009 at 5:42 am
Hi,
I have stored procedure which assings tickets to the users , iam facing a problem here where more than one users are getting the same tickets , could some one help me out here , below are the steps in the stored procedure:
1)Select the tickets that were Manually assigned to the user by the manger & put them into temp table.
2)Select Top 5 tickets with locked=0 & put them into a temporary table created in first step.
3)Update the table where tickets are retreived with locked -1 indicating they cant be retrieved again.
4)select the final result from the temporary table.
Thanks in Advance.
October 27, 2009 at 5:48 am
Post code?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2009 at 6:26 am
Without code it's hard to know what's happening, but if you're loading data into a temp table and then locking it in a second step, there's your problem. You need to lock it in a single step, not load it into temporary tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 2, 2009 at 9:29 pm
Curious,you looking for something along these lines?
create table dbo.Ticket (
TicketId int identity(1,1),
UserAssigned varchar(32),
UserData varchar(512) null,
TicketLocked smallint default 0
);
go
insert into dbo.Ticket (
UserAssigned
)
select 'Able' union all
select 'Able' union all
select 'Cain' union all
select 'John' union all
select 'Able' union all
select 'John' union all
select 'Cain' union all
select 'Able' union all
select 'John' union all
select 'Cain' union all
select 'Able' union all
select 'John' union all
select 'Cain' union all
select 'Able' union all
select 'John' union all
select 'Cain' union all
select 'Able' union all
select 'John' union all
select 'Cain' union all
select 'Able' union all
select 'John' union all
select 'Cain';
go
select
*
from
dbo.Ticket;
go
declare @UserAssigned varchar(32);
set @UserAssigned = 'Cain';
with Top5 (
RowNum,
TicketId,
UserAssigned
) as (
select
row_number() over (order by TicketId),
TicketId,
UserAssigned
from
dbo.Ticket
where
TicketLocked = 0
and UserAssigned = @UserAssigned
)
update top (5) dbo.Ticket set
TicketLocked = -1
output DELETED.TicketId, DELETED.UserAssigned, DELETED.UserData
from
dbo.Ticket t
inner join Top5 t5
on (t.TicketId = t5.TicketId)
where
RowNum <= 5;
go
select
*
from
dbo.Ticket;
go
drop table dbo.Ticket;
go
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply