Извлечь значение, возвращенное из динамического SQL

У меня есть хранимая процедура, которая генерирует и выполняет часть динамического T-SQL, которая после создания выглядит так:

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'

Этот запрос может вернуть несколько тысяч строк в среде разработки и значительно больше в реальном времени.

Я хочу узнать, сколько строк возвращает динамический запрос, прежде чем я действительно верну результаты, чтобы, если число превышает некоторый предел, я мог вернуть сообщение об ошибке "уменьшить ваш запрос".

Я попытался сгенерировать еще один фрагмент SQL следующим образом:

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

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

где @sql — динамический запрос. Затем я со смущением понял, что EXEC(@sqlrowcount) всегда будет возвращать 1, поскольку возвращает одну запись, значение которой равно количеству записей.

Есть ли (относительно) элегантный способ сделать это, например. без записи результата во временную таблицу?


sql-server tsql dynamic-sql
person Arj    schedule 30.08.2012    source источник

Ответы (2)


arrow_upward
6
arrow_downward

В одну сторону;

--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 для принудительного ограничения, запуск одного и того же оператора дважды не очень эффективен.

person Alex K.    schedule 30.08.2012

arrow_upward
2
arrow_downward

Проблема, с которой вы сталкиваетесь, заключается в том, что операторы exec сохраняют предыдущий @@rowcount значение, которое в вашем случае равно 1 из оператора set (все операторы set делают @@rowcount равным 1). Это необходимо, поскольку execute создает собственный пакет.

Лучший способ получить значение — использовать sp_executesql с выходным параметром. Это будет выглядеть примерно так:

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

Это использует способность sp_executesql иметь выходные параметры для возврата значения из счетчика.

Одним из хороших источников дополнительных сведений о динамических запросах в целом, который я рекомендую всем серьезным SQL-программам, является The Curse and Blessing of Динамический SQL, который объясняет, как параметризовать sp_executesql и почему вам может понадобиться это, а также несколько других связанных тем.

person TimothyAWiseman    schedule 30.08.2012