CREATE PROCEDURE [dbo].[Sp_Queue] @NumberQueue Smallint Output , @FlagQueue Char(1), @UserId SMALLINT, @Status CHAR(1), @StartQueue SMALLINT = NULL
AS

DECLARE @Queue AS TABLE
(
  Row INT, 
  Queue int
)

INSERT INTO @Queue(Row,Queue)
SELECT ROW_NUMBER() OVER(ORDER BY Queue) + (CASE WHEN @FlagQueue= 0 THEN @StartQueue - 1 ELSE 0 END) Row, Queue FROM (
SELECT Queue FROM dbo.TblQueue WITH (NOLOCK)
WHERE DATEDIFF(DAY, DateDay, GETDATE()) = 0
UNION 
SELECT NumberQueue Queue FROM dbo.TmpDrugH WITH (NOLOCK) WHERE Kind_Flag =1
AND NumberQueue IS NOT NULL
UNION 
SELECT NumberQueue Queue FROM dbo.DrugH WITH (NOLOCK) WHERE Kind_Flag =1
And DATEDIFF(DAY, Tarikh, GETDATE()) = 0
AND NumberQueue IS NOT NULL
) AS T 
ORDER BY Queue

SELECT TOP 1 @NumberQueue = Row FROM @Queue
WHERE Row NOT IN(SELECT Queue FROM @Queue)

IF ISNULL(@NumberQueue,0) = 0 
  SET @NumberQueue = (SELECT ISNULL(Max(Queue),0) + 1 From @Queue) 

INSERT Into TblQueue (Queue, StartTime, FlagQueue, User_id,QStatus,QSound)
Values(@NumberQueue, Convert(Varchar(5), Getdate(),108),@FlagQueue, @UserId,@Status, @Status)

Return @NumberQueue
