提取从动态SQL返回的值 [英] Extract value returned from dynamic SQL

问题描述

我有一个存储过程，该过程可以生成并执行一段动态的T-SQL，一旦构建，它就会像这样

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.

我尝试生成另一条这样的SQL：

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

其中 @sql 是动态查询。然后，我尴尬地意识到， EXEC（@sqlrowcount）将始终返回 1 ，因为它返回的一条记录的 value 是记录的数量。

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?

推荐答案

一种方法；

--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

您还可以使用 TOP 来强制执行限制，两次运行同一条语句效率不高。

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

这篇关于提取从动态SQL返回的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！