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

Версия 11:29, 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