Retrieving a value from dynamic SQL

Question

I have a stored procedure that generates and executes a piece of dynamic T-SQL, which after creating it 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 significantly more in live mode.

I want to know how many lines a dynamic query returns before I return the results, so if the number is greater than a certain limit, I can return the error “narrow your query”.

I tried to create another SQL fragment:

 DECLARE @sqlrowcount NVARCHAR(MAX); SET @sqlrowcount = 'SELECT COUNT(*) FROM (' + @sql + ') AS TEMP'; EXEC(@sqlrowcount); IF @@ROWCOUNT > @limit BEGIN .... END 

where @sql is a dynamic query. Then I confusedly realized that EXEC(@sqlrowcount) will always return 1 because it returns a single record whose value is the number of records.

There is a (relatively) elegant way to do this, for example. without writing the result to the temporary table?

Answer 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 can also use TOP to enforce a constraint; executing the same statement twice is not very efficient.

Answer 2

The problem you're working with is that the exec statements keep the previous value @@ rowcount , which in your case is 1 from the set statement (all the given commands do @@ rowcount equal to 1). This is necessary because run creates its own batch.

The best way to get the value is to use sp_executesql with an output parameter. It looks something like this:

 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 the sp_executesql ability to have output parameters to return a value from the account.

One good source for more detailed information on dynamic queries in general, which I recommend to all serious SQL programs, is the Curse and blessing of dynamic SQL , which explains how to parameterize sp_executesql and why you might want to along with a few other related topics.

Answer 3

 Declare @Rowcount int SELECT @RowCount = count(1) FROM dbo.tblUserAuditLog LEFT OUTER JOIN dbo.tblUsers ON dbo.tblUserAuditLog.intUserIdFK = dbo.tblUsers.intUserId 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] , @RowCount FROM dbo.tblUserAuditLog LEFT OUTER JOIN dbo.tblUsers ON dbo.tblUserAuditLog.intUserIdFK = dbo.tblUsers.intUserId