Extract value returned from dynamic SQL

Question

I have a stored procedure which generates and executes a piece of dynamic T-SQL which, once built up, looks like this

SELECT 
    tblUsers.strUserName AS [Username]
    ,tblUsers.strEmail AS [Email]
    ,tblUserAuditLog.strIpAddress AS [IP Address]
    ,tblUserAuditLog.dtAuditTimeStamp AS [Timestamp]
    ,tblUserAuditLog.strAuditLogAction AS [Action]
    ,tblUserAuditLog.strLogDetails AS [Details]
FROM         
    tblUserAuditLog 
        LEFT OUTER JOIN tblUsers 
        ON tblUserAuditLog.intUserIdFK = tblUsers.intUserId
WHERE 
    tblUsers.strUserName = 'a12jun'
    AND tblUserAuditLog.dtAuditTimeStamp >= '2012-08-10'

This query can return several thousand rows in the dev environment and will return considerably more in live.

I want to find out how many rows the dynamic query returns before I actually return the results, so that if the number is more than some limit, I can return a 'narrow your query' error message.

I have tried generating another piece of SQL like this:

DECLARE @sqlrowcount NVARCHAR(MAX);
SET @sqlrowcount = 'SELECT COUNT(*) FROM (' + @sql + ') AS TEMP';
EXEC(@sqlrowcount);

IF @@ROWCOUNT > @limit BEGIN .... END

where @sql is the dynamic query. I then embarrassingly realised that EXEC(@sqlrowcount) will always return 1, because it returns one record whose value is the number of records.

Is there a (relatively) elegant way of doing this, e.g. without writing the result to a temporary table?

Solution 1

One way;

--base sql
declare @sql  nvarchar(255) = N'select * from master.dbo.spt_values'

--count wrapper
declare @sqlb nvarchar(255) = N'set @count=(select count(*) from (' + @sql + ') T)'

declare @count int
exec sp_executesql @sqlb, N'@count int output', @count output

select 'rows=',@count

You could also use TOP to enforce a limit, running the same statement twice is not very efficient.

Solution 2

The problem you are running into is that exec statements preserve the previous @@rowcount value, which in your case is 1 from the set statement (all set statements make @@rowcount become 1). This is necessary because execute creates its own batch.

The best way to get the value is to use sp_executesql with an output paramater. That would look something like:

declare @numRows int
declare @sql nvarchar(max)
set @sql = N'Select @numRows= count(*) from dbo.temp'

exec sp_executesql @sql, N'@numRows int output', @numRows output

--Put your if statement here using @numRows

This uses sp_executesql's ability to have output parameters to return the value from the count.

One good source for more details on dynamic queries in general that I recommend to all serious SQL programms is The Curse and Blessing of Dynamic SQL which explains how to paramaterize sp_executesql and why you might want to along with several other related topics.