ALTER PROCEDURE [dbo].[usp_PhoneCallCheck] 
	-- Add the parameters for the stored procedure here
	@Brand nvarchar(20),
	@TimeOfCall DateTime --nvarchar(8)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
	DECLARE @BrandId nvarchar(2)
	DECLARE @DRStatus int
	DECLARE @ReturnValue int
 
	set @ReturnValue = 0
 
	SELECT @BrandId = fldId FROM tblBrands where fldBrand = @Brand		-- Get the Brand ID based upon the name of the brand that was passed in
		
	-- Below check the time and day of the incoming call and see if it can match 
 
	If Exists(
		SELECT * FROM tblOpeningTimes Where
		fldBrandId = @BrandId 
		AND
		CONVERT(datetime,'2009/09/14 ' + CONVERT(varchar(12),@TimeOfCall,114)) BETWEEN fldOpeningTime AND fldClosingTime
		)
		AND
		Exists(
		SELECT * FROM tblOpenDays Where fldBrandId = @BrandID AND fldDayOfWeek = DateName(WeekDay,getdate())
		)
		set @ReturnValue = 2
	Else
		set @ReturnValue = 3
 
	-- Check the DR state and override if we are in a DR mode
 
	If @ReturnValue = 2
	Begin
		SELECT @DRStatus = fldStatus FROM tblDR where fldId = 1				-- Get the DR status of the call center
 
		If @DRstatus = 1													-- If the DR status = 1 then we are closed and set the return value = 1
			set @ReturnValue = 1
	End
	
	Return(@ReturnValue)
END
 
 
******************************************************************
******************************************************************
 
USE [CallControl]
GO
 
DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[usp_PhoneCallCheck]
		@Brand = N'Test Brand',
		@TimeOfCall = N'10:21:54'
 
SELECT	'Return Value' = @return_value
 
GO
 
*******************************************************************
*******************************************************************
 
Str_Connect = "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=CallControl;Data Source=swyxsrv02\sqlexpress"
Set CnnSQL=CreateObject("ADODB.Connection")
CnnSQL.Open Str_Connect, "CallControl_User", "CallC0ntr0l"
 
 
  strSQL = "exec usp_PhoneCallCheck 'Test Brand','10:21:54' "
  CnnSQL.Execute(strSQL)
 
cnnSQL.Close
Set cnnSQL = Nothing