Stored Procedure to Count Rows in Dynamic SQL

The first parameter contains the dynamic SQL string. The second parameter is an optional column name the count statement operates on; the default is *.

/*
 Counts the number of records in a dynamic sql string.  Example usage:
 declare @records int
 exec @records =  usp_CountRowsInDynamicSQL   'select recordId=5 union select recordId=6', 'recordId'
 print @records
 
*/
Create proc  usp_CountRowsInDynamicSQL (@sql varchar(800), @colNameToCount varchar(50) = '*' ) AS
 declare @rowCount int
 create table #tmpRowCount (row_Count int) 
 set nocount on
 set @sql = 'insert into #tmpRowCount (row_Count) select count(' + @colNameToCount + ') from ( ' + @sql + ') x'
 exec(@sql)
 set @rowCount = (select top 1 row_count from #tmpRowCount)  
 set @rowCount = isnull(@rowCount,0)   
 set nocount off
 drop table #tmpRowCount
 return @rowCount