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?
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With