ALTER PROCEDURE [dbo].[usp_BusinessUser_Search] (	@Business nVarChar(255) = NULL
													, @Location nVarChar(255) = NULL
													, @PageNumber Int = 1
													, @RecordsPerPage Int = 10
													, @TotalRecords Int OUTPUT)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(MAX)
			, @CacheTable SYSNAME
			, @TotalRows Int
			, @Category VarChar(255)
			, @BusinessCategory Int
			, @TownCounty VarChar(50)
			, @PcodeTownCounty VarChar(50)

	INSERT	Voucher_SearchHistory (Keyword, Location)
	SELECT	NULLIF(@Business,''), NULLIF(@Location,'')

	-- ============================================================
	-- Set Filter Options
	-- ============================================================
	-- Location
	SET @Location = NULLIF(NULLIF(NULLIF(@Location,''),'UK'),'United Kingdom')

	IF @Location IS NOT NULL
	BEGIN
		-- Match to location
		SELECT	@PcodeTownCounty = PCodeTownCounty
		FROM	dbo.Res_PostcodeDistrict
		WHERE	PCodeTownCounty LIKE '' + @Location + '%'

		SELECT	@TownCounty = TownCounty
		FROM	dbo.Res_PostcodeDistrict
		WHERE	TownCounty LIKE '' + @Location + '%'
	END

	-- Category / Business
	SET @Business = NULLIF(NULLIF(@Business,''),'All')

	-- Match to category
	SELECT	@BusinessCategory = b.CategoryID
	FROM	Res_BusinessCategory b
			LEFT OUTER JOIN Res_BusinessSubCategory bsc on b.CategoryId = bsc.CategoryID
	WHERE	Category like '' + @Business + '%' OR SubCategory LIKE '' + @Business + '%'

	-- ============================================================
	-- New Cached Table Name
	-- ============================================================
	SELECT @CacheTable = REPLACE('[Cache_' + REPLACE(COALESCE(NULLIF(ISNULL(CAST(@BusinessCategory AS VarChar), REPLACE(@Business, '''', '')),''),'All') + '_' +  COALESCE(@Location, 'All'),' ','') + ']','''','¬')

	-- ============================================================
	-- Create cached table if doesn't already exist
	-- ============================================================
	IF OBJECT_ID(@CacheTable) IS NULL
	BEGIN
		IF @BusinessCategory IS NULL AND @BusinessCategory IS NULL
		BEGIN
			-- Get Businesses
			SET @SQL = '	SELECT	id
							INTO	#wmwtmp
							FROM	dbo.Res_CompanyName
							WHERE	companyname = ''' + @Business + '''

							IF (SELECT COUNT(*) FROM #wmwtmp) = 0
							BEGIN
								INSERT INTO #wmwtmp
								SELECT	id
								FROM	Res_CompanyName t
										INNER JOIN FREETEXTTABLE ( Res_CompanyName , * , ''FORMSOF(INFLECTION , ' + @Business + ')'') ft ON ( t.id = ft.[Key])
							END

							SELECT	TOP 100 CAST(NULL AS INT) AS TotalRows
									, ROW_NUMBER() OVER (ORDER BY IsPetoba, BusinessID ASC) AS RowNoID
									, BusinessID
									, SiteID
									, CompanyName
									, Address1
									, Address2
									, Address3
									, Town
									, County
									, Postcode
									, Tel
									, CompanyDesc
									, VoucherID
									, Title
									, [Description]
									, Link
									, ExpiryDate
									, ImageLink
									, Status
									, DateStamp
									, StartDate
									, VoucherSiteID
									, Code
									, Type
									, VoucherTandC
									, PCDist
									, PCodeTownCounty
									, TownCounty
									, BusinessCategory
									, IsPetoba
							INTO	dbo.' + @CacheTable + '
							FROM	dbo.Businesses_Voucher_Flat a
									INNER JOIN #wmwtmp b ON a.RecordID = b.ID
							WHERE	IsDeleted = 0
							' + CASE WHEN @TownCounty IS NOT NULL THEN 'AND TownCounty = ''' + @TownCounty + '''' ELSE '' END + '
							' + CASE WHEN @PCodeTownCounty IS NOT NULL THEN 'AND PCodeTownCounty = ''' + @PCodeTownCounty + '''' ELSE '' END + ''
		END
		ELSE
		BEGIN
			SET @SQL =	'SELECT	TOP 100 CAST(NULL AS INT) AS TotalRows
								, ROW_NUMBER() OVER (ORDER BY IsPetoba, BusinessID ASC) AS RowNoID
								, BusinessID
								, SiteID
								, CompanyName
								, Address1
								, Address2
								, Address3
								, Town
								, County
								, Postcode
								, Tel
								, CompanyDesc
								, VoucherID
								, Title
								, [Description]
								, Link
								, ExpiryDate
								, ImageLink
								, Status
								, DateStamp
								, StartDate
								, VoucherSiteID
								, Code
								, Type
								, VoucherTandC
								, PCDist
								, PCodeTownCounty
								, TownCounty
								, BusinessCategory
								, IsPetoba
						INTO	dbo.' + @CacheTable + '
						FROM	dbo.Businesses_Voucher_Flat a
						WHERE	IsDeleted = 0
						' + CASE WHEN @BusinessCategory IS NOT NULL THEN 'AND BusinessCategory = ' + CAST(@BusinessCategory AS VarChar) + '' ELSE CASE WHEN @Business IS NOT NULL THEN 'AND CompanyName = ''' + REPLACE(@Business, '''', '''''') + '''' ELSE '' END END + '
						' + CASE WHEN @TownCounty IS NOT NULL THEN 'AND TownCounty = ''' + @TownCounty + '''' ELSE '' END + '
						' + CASE WHEN @PCodeTownCounty IS NOT NULL THEN 'AND PCodeTownCounty = ''' + @PCodeTownCounty + '''' ELSE '' END + ''

			END

			SET @SQL = @SQL + ' UPDATE	c
								SET		TotalRows = m.MaxRowNoID
								FROM	' + @CacheTable + ' c, (SELECT MAX(RowNoID) AS MaxRowNoID FROM ' + @CacheTable + ') m'
		--PRINT @SQL
		EXEC (@SQL)
	END

	-- ============================================================
	-- Return Results
	-- ============================================================
	SET @SQL = 'SELECT	BusinessID
						, SiteID
						, CompanyName
						, Address1
						, Address2
						, Address3
						, Town
						, County
						, Postcode
						, Tel
						, CompanyDesc
						, VoucherID
						, Title
						, [Description]
						, Link
						, ExpiryDate
						, ImageLink
						, [Status]
						, DateStamp
						, StartDate
						, VoucherSiteID
						, Code
						, Type
						, VoucherTandC
						, IsPetoba
				FROM	dbo.' + @CacheTable + '
				WHERE	RowNoID BETWEEN ' + CAST(1 + (@RecordsPerPage*(@PageNumber-1)) AS VARCHAR) + ' AND '  + CAST(@RecordsPerPage*@PageNumber AS VARCHAR) + ''
	EXEC (@SQL)

	/*
	SELECT	TOP 10 BusinessID
			, SiteID
			, CompanyName
			, Address1
			, Address2
			, Address3
			, Town
			, County
			, Postcode
			, Tel
			, CompanyDesc
			, VoucherID
			, Title
			, [Description]
			, Link
			, ExpiryDate
			, ImageLink
			, [Status]
			, DateStamp
			, StartDate
			, VoucherSiteID
			, Code
			, Type
			, VoucherTandC
	FROM	Businesses_Voucher_Flat
*/

	-- Find new Total (Filtered)
	SET @SQL = 'SELECT @TotalRows = COUNT(1) FROM ' + @CacheTable + ''
	EXEC sp_EXECUTESQL @SQL,N'@TotalRows INT OUTPUT',@TotalRows OUTPUT
	SET @TotalRecords = @TotalRows
END