Stored Procedure - Auto rotate staff with where clause.

[ssmeshack]

Hai all,

I have problem here. Im using VWD with C#. Database Sql Server 2005. I have done auto rotation for staffname where autorotation = 1.

It was no problem until I add a new user with autorotation = 0. Where in adding new ticket form the staffname which is autorotate will appear in the label. But here it only show the new user that I add. It didn't rotate where actually it should not show the staffname if autorotation = 0.

here is the code:

CREATE PROCEDURE GetRotation
 AS
 
declare @counter  int
 
 
select @counter = count (*)   from staff where staffid >
(
                select top 1 st.staffid from ticket tc
                inner join staff st on st.staffname = tc.ticowner
       where st.autorotation = 1
       order by ticid desc
)
 
if @counter <> 0
begin
                select top 1 staffname from staff where staffid >
                (
                                select top 1 st.staffid from ticket tc
                                inner join staff st on st.staffname = tc.ticowner
             where st.autorotation = 1
                                order by ticid desc
                )
 
end
 
else
begin
                select top 1 staffname from staff where autorotation = 1
                order by staffid asc
end
GO

Can anyone tell me where is the problem in the sp? Thanks in advance.

-Meshack