Примеры пользовательских процедур — различия между версиями
Материал из Oktell
(Новая страница: «Наверх <pre> if (@idtask = 'B8A22910-BFA4-480D-BFB1-151D0C06F93B') begin select @Id = operatorid from TableMain where Id = @…») |
|||
Строка 28: | Строка 28: | ||
<pre> | <pre> | ||
− | + | IF (@idtask = 'B77E5601-A103-4A41-B1FD-6E82EEB6B46A') | |
− | + | BEGIN | |
− | + | DECLARE @state INT | |
− | + | SELECT @ID = idoperator | |
− | + | FROM clients | |
− | + | WHERE id = @idabonent | |
SELECT top 1 @state = h.state | SELECT top 1 @state = h.state | ||
FROM A_Users u | FROM A_Users u | ||
− | LEFT JOIN (SELECT h.* | + | LEFT JOIN |
− | + | (SELECT h.* | |
− | + | FROM | |
− | + | (SELECT UserId, max(TimeChange) TimeChange | |
− | + | FROM A_UserStateHistory | |
− | + | GROUP BY UserId ) t | |
− | + | JOIN A_UserStateHistory h | |
− | + | ON t.UserId = h.UserId AND h.TimeChange = t.TimeChange | |
− | + | ) h ON h.UserId = u.Id | |
− | WHERE u.id = @ID | + | WHERE u.id = @ID |
− | + | IF (@state= 0 or @state is null) | |
− | + | BEGIN | |
− | + | SELECT u.Id | |
− | + | FROM A_Users u | |
− | + | INNER JOIN A_UserParams up | |
− | + | ON u.Id = up.Iduser | |
− | + | WHERE IsOperator = 1 | |
− | + | ORDER BY NEWID() | |
− | + | END | |
− | + | ELSE | |
− | + | BEGIN | |
− | + | SELECT @ID Id | |
− | + | END | |
+ | END | ||
</pre> | </pre> |
Версия 11:19, 19 декабря 2014
if (@idtask = 'B8A22910-BFA4-480D-BFB1-151D0C06F93B') begin select @Id = operatorid from TableMain where Id = @idabonent if (@Id is null) begin select u.Id from A_Users u inner join A_UserParams up on u.Id = up.Iduser where IsOperator = 1 order by NEWID() end else begin select @Id Id end
IF (@idtask = 'B77E5601-A103-4A41-B1FD-6E82EEB6B46A') BEGIN DECLARE @state INT SELECT @ID = idoperator FROM clients WHERE id = @idabonent SELECT top 1 @state = h.state FROM A_Users u LEFT JOIN (SELECT h.* FROM (SELECT UserId, max(TimeChange) TimeChange FROM A_UserStateHistory GROUP BY UserId ) t JOIN A_UserStateHistory h ON t.UserId = h.UserId AND h.TimeChange = t.TimeChange ) h ON h.UserId = u.Id WHERE u.id = @ID IF (@state= 0 or @state is null) BEGIN SELECT u.Id FROM A_Users u INNER JOIN A_UserParams up ON u.Id = up.Iduser WHERE IsOperator = 1 ORDER BY NEWID() END ELSE BEGIN SELECT @ID Id END END