title: Output Parameter from Dynamic Sql
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>