Cacher is the code snippet organizer for pro developers

USE [QFlow59SP1]
GO

/****** Object:  StoredProcedure [cqf].[CustomerGetAppointmentsCountToday]    Script Date: 8/3/2016 3:15:33 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Hem Acharya
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [cqf].[CustomerGetAppointmentsCountToday]
-- Add the parameters for the stored procedure here

	@TopUnitId int,
	@CustomerId int,
	@ServiceId int,
	@Counter bit OUTPUT
AS

  -- Tal - Identation.
  -- Tal - Capital letters.
  -- Tal - Add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  
BEGIN
    DECLARE @t1 TABLE (
      ProcessId int,
      CaseID int,
      CustomerId int,
      UnitPath nvarchar(2000),
      UnitName nvarchar(50),
      ServiceId int,
      ServiceName nvarchar(50),
      AppointmentDate datetime,
      AppointmentId int,
      PreventAutoQueue bit,
      UnitIdPath varchar(400),
      TIMEFDIFF int,
      TIMEDIFFVALUE int,
      CompositeAppointmentId int,
      IsCustomerGroupAppointment bit,
      CurrentEntityStatus tinyint
    );

    DECLARE @t2 TABLE (
      TableIndex int IDENTITY,
      ProcessId int,
      CaseId int,
      ServiceId int,
      ServiceName nvarchar(50),
      CurrentEntityStatus tinyint
    );

    INSERT INTO @t1 (ProcessId,
		CaseId,
		CustomerId,
		UnitPath,
		UnitName,
		ServiceId,
		ServiceName,
		AppointmentDate,
		AppointmentId,
		PreventAutoQueue,
		UnitIdPath,
		TIMEFDIFF,
		TIMEDIFFVALUE,
		CompositeAppointmentId,
		IsCustomerGroupAppointment,
		CurrentEntityStatus)
    EXEC qf.CustomerGetAppointmentsToday @TopUnitId,
                                         @CustomerId


    INSERT INTO @t2 (ProcessId, CaseId, ServiceId, ServiceName, CurrentEntityStatus)
      SELECT
        ProcessId,
        CaseID,
        ServiceId,
        ServiceName,
        CurrentEntityStatus
      FROM @t1 t


    DECLARE @appointmentCount int

    SET @appointmentCount = @@ROWCOUNT

    DECLARE @loopVar int
    DECLARE @validAppointmentsCount int

    SET @validAppointmentsCount = 0;
    SET @loopVar = 1

    WHILE (@loopVar <= @appointmentCount)
    BEGIN
      SELECT
        *
      FROM @t2 t2
      WHERE t2.TableIndex = @loopVar
      AND t2.CurrentEntityStatus = 0
      AND t2.ServiceId = @ServiceId

      IF @@ROWCOUNT = 1
        SET @validAppointmentsCount = @validAppointmentsCount + 1;

		SET @loopVar = @loopVar + 1;

    END

    IF @validAppointmentsCount >= 1
      SET @Counter = 1
    ELSE
      SET @Counter = 0

  END


GO