CREATE PROCEDURE [dbo].[usp_COUNTDocumentSearchResults]
(
   @clientID INT 
,  @accountNumber VARCHAR(15) = NULL
,  @invoiceNumber VARCHAR(20) = NULL
,  @firstDate DATETIME = NULL
,  @secondDate DATETIME = NULL
,  @handsetNumber VARCHAR(12) = NULL
,  @recipientName VARCHAR(45) = NULL
,  @count INT OUTPUT
)
AS
SET NOCOUNT ON

SET @handsetNumber = REPLACE(@handsetNumber, ' ', '')

DECLARE @sql NVARCHAR(4000)

SET @sql = 'SELECT COUNT(DS.DocumentSetID)
		FROM Client C 
		INNER JOIN Account A 
		ON (
		(C.ClientID = A.ClientID)
		AND (A.ClientID = ' + CONVERT(VARCHAR,@clientID) + ')'

IF @accountNumber IS NOT NULL
	BEGIN
		IF CHARINDEX('*',@accountNumber) = 0
		BEGIN
		SET @sql = @sql + ' AND (AccountNumber = ''' + @accountNumber + ''')'
		END 
			ELSE IF CHARINDEX('*',@accountNumber) = 1
			BEGIN
			SET @sql = @sql + ' AND (AccountNumber LIKE ''%' + REPLACE(@accountNumber,'*','') + ''')'
			END 
				ELSE 
				BEGIN
				SET @sql = @sql + ' AND (AccountNumber LIKE ''' + REPLACE(@accountNumber,'*','') + '%'')'
				END 
	END

SET @sql = @sql + ' )'

SET @sql = @sql + ' INNER JOIN DocumentRecipient DR 
					ON (
						(A.AccountID = DR.AccountID)'

IF @recipientName IS NOT NULL
	BEGIN
		SET @sql = @sql + ' AND (DocumentRecipientName LIKE (''%' + @recipientName  + '%''))'
	END

SET @sql = @sql + ' )'


SET @sql = @sql + ' INNER JOIN Document D 
					ON (
					 	(DR.DocumentRecipientID = D.DocumentRecipientID)'

IF @invoiceNumber IS NOT NULL
BEGIN
	SET @sql = @sql + ' AND (DocumentInvoiceNumber = '''+ @invoiceNumber + ''')'
END


IF (@firstDate IS NOT NULL)
BEGIN
	IF @secondDate IS NULL
	BEGIN
		SET @sql = @sql + 'AND (DocumentDate = ''' + CONVERT(VARCHAR,@firstDate) + '''' + ')'
	END
		ELSE IF @secondDate IS NOT NULL 
		BEGIN
			SET @sql = @sql + 'AND (DocumentDate BETWEEN ''' + CONVERT(VARCHAR,@firstDate) + '''' + ' AND ' + '''' + CONVERT(VARCHAR,@secondDate) + '''' + ')'
		END
END

SET @sql = @sql + ' )'

IF @handsetNumber IS NOT NULL
BEGIN
	SET @sql = @sql + ' INNER JOIN Handset H 
						ON (
							D.DocumentID = H.DocumentID
							AND (HandsetNumber = ''' + @handsetNumber + '''' + ')
						   )'
END

SET @sql = @sql + ' INNER JOIN DocumentSet DS ON DS.DocumentSetID = D.DocumentSetID
		INNER JOIN [File] F ON F.FileID = DS.FileID
		INNER JOIN Status S ON (S.StatusID = F.StatusID AND StatusName = ''Accessible'')'


EXEC sp_executesql @sql
GO

SELECT TOP @limitVal DS.DocumentSetID
		, D.DocumentDate
		, A.AccountNumber
		FROM Client C 
			INNER JOIN Account A .......etc

SELECT TOP ' + CONVERT(VARCHAR,@limit) + '  DS.DocumentSetID
		, D.DocumentDate
		, A.AccountNumber..................

SET ROWCOUNT @limitVal

SELECT TOP @limitVal DS.DocumentSetID
		, D.DocumentDate
		, A.AccountNumber
		FROM Client C 
			INNER JOIN Account A .......etc
ORDER BY ... sequence columns ...

SET ROWCOUNT 0 -- Reset to All Rows - VERY important!