Thursday, July 01, 2010

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

No comments: