I wrote this procedure to get records page wise for a gridview. All was fine, but now it is also required to get a count of records as output parameter. For eg if the total records that match a name are 100, then the query should result some of the records and also output the number 100. The records part is working. How can I get the count too.
ALTER STORED PROCEDURE GetData
@SearchText nvarchar(50),
@SortOrder nchar(10),
@ColName nvarchar(20),
@StartIndex int,
@PageSize int,
@RecCount int output
AS
BEGIN
DECLARE @Query nvarchar(max), @Params nvarchar(max)
IF @SearchText = ''
SET @SearchText = null
ELSE
SET SearchText = '''%'+@SearchText+'%'''
SET @Params = '@StartIndex int, @PageSize int, @RecCount int output'
SET @Query = 'WITH TBL AS
(
SELECT * FROM tblEmployee
WHERE ('+@ColName+' LIKE '+@SearchText+' OR '+@SearchText+'
IS NULL) AND DELETED = 0;
SELECT @RecCount = @@ROWCOUNT
)
SELECT ROW_NUMBER() OVER(ORDER BY '+@ColName+' '+@SortOrder+'
)Row, * INTO #Result FROM TBL
SELECT * FROM #Result Where Row BETWEEN @StartIndex
AND @PageSize
DROP TABLE #Result'
Execute sp_Executesql @Query, @Params, @StartIndex,@PageSize, @RecordCount output
SELECT @RecCount