Примеры пользовательских процедур — различия между версиями

Материал из 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')   
+
  IF (@idtask = 'B77E5601-A103-4A41-B1FD-6E82EEB6B46A')   
  begin
+
  BEGIN
 
   
 
   
  declare @state int
+
  DECLARE @state INT
  
  select @ID = idoperator
+
  SELECT @ID = idoperator
  from clients
+
  FROM clients
  where id = @idabonent
+
  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  
    FROM  
+
        (SELECT h.*
      (
+
        FROM  
  SELECT UserId, max(TimeChange) TimeChange
+
            (SELECT UserId, max(TimeChange) TimeChange
  FROM A_UserStateHistory  
+
            FROM A_UserStateHistory  
        GROUP BY UserId
+
            GROUP BY UserId ) t
  ) t
+
        JOIN A_UserStateHistory h  
    JOIN A_UserStateHistory h ON t.UserId = h.UserId AND h.TimeChange = t.TimeChange
+
        ON t.UserId = h.UserId AND h.TimeChange = t.TimeChange
    ) h ON h.UserId = u.Id
+
) h ON h.UserId = u.Id
WHERE u.id  = @ID
+
WHERE u.id  = @ID
  
    if (@state= 0 or @state is null)
+
IF (@state= 0 or @state is null)
    begin
+
  BEGIN
  select u.Id
+
    SELECT u.Id
    from A_Users u
+
    FROM A_Users u
      inner join A_UserParams up on u.Id = up.Iduser
+
    INNER JOIN A_UserParams up  
    where IsOperator = 1
+
    ON u.Id = up.Iduser
    order by NEWID()
+
    WHERE IsOperator = 1
  end
+
    ORDER BY NEWID()
  else
+
  END
  begin
+
ELSE
    select @ID Id
+
  BEGIN
   end
+
    SELECT @ID Id
  end
+
   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