Storeprocedure that filter, sort, support paging and do some extra functionality on the resultset

Storeprocedure that filter, sort, support paging and do some extra
functionality on the resultset

I'm looking for an expert advice to fine tune the sample stored procedure
for performance gain as well as to follow best practices.
To explain my requirements I have created a sample procedure below. The
procedure need to do the following
Need to filter based on the parameters.
Sort the data based on the @SortExpression eg. Code Desc or ImpDate ASC
Return total record count of entire record set.
Return totals of a field in the entire record set .
finally return only a subset of record set based on the startRowIndex and
page size
CREATE PROCEDURE [dbo].[_getlist](@CompanyID VARCHAR(10),
@IsPaid VARCHAR(3),
@Code VARCHAR(10) = NULL,
@ImpDate DATETIME = NULL,
@BatchNo INT,
@StartRowIndex INT,
@PageSize INT,
@SortExpression VARCHAR(50),
@TotalAmount NUMERIC(15, 2) output,
@RecordCount INT output)
AS
BEGIN
DECLARE @SortDirection VARCHAR(10)

SET @SortDirection = 'ASC'

IF RIGHT(@SortExpression, 5) = ' DESC'
SET @SortDirection = 'DESC'

DECLARE @SortColumn VARCHAR(50)

SELECT @SortColumn = Replace(@SortExpression, ' ASC', '')

SELECT @SortColumn = Replace(@SortColumn, ' DESC', '')

DECLARE @StartIndex INT,
@EndIndex INT

SET @StartIndex = @StartRowIndex
SET @EndIndex = @StartRowIndex + @PageSize -- (@CurrentPage + 1 )
SELECT data.,
Row_number()
OVER (
ORDER BY CASE WHEN @SortDirection = 'DESC' THEN CASE WHEN
@SortColumn
=
'code' THEN data.client_code END END DESC, CASE WHEN
@SortDirection
=
'ASC'
THEN CASE WHEN @SortColumn = 'code' THEN data.client_code
END END
ASC )
AS
RowNumber
INTO #temptable
FROM (SELECT *
FROM clients
WHERE is_local = 'Yes'
AND is_paid = 'No'
AND status = 'Valid'
AND ( company_id = @CompanyID )
AND ( ispaid = @IsPaid
OR @IsPaid IS NULL )
AND ( code = @Code
OR @Code IS NULL )
AND ( @ImpDate IS NULL
OR import_date = @ImpDate )
AND ( @BatchNo = 0
OR batch_no = @BatchNo )) AS data

SELECT @RecordCount = Count(*)
FROM #temptable

SELECT @TotalAmount = Sum(total_tax)
FROM #temptable

SELECT *
FROM #temptable
WHERE rownumber >= @StartIndex
AND ( rownumber <= @EndIndex
OR @PageSize = -1 )

DROP TABLE #temptable
END
The sample is just to explain the requirements. I really appreciate any
help that could help me out.
Thanks,