Как получить количество строк из EXEC() в TSQL SPROC?
У меня есть TSQL sproc, который строит запрос как и выполняет его следующим образом:
EXEC (@sqlTop + @sqlBody + @sqlBottom)
@sqlTop содержит что-то вроде SELECT TOP (x) col1, col2, col3...
TOP (x) ограничит возвращаемые строки, поэтому позже я хочу знать, какое фактическое количество строк в таблице соответствует запросу.
Затем я заменю @sqlTop на что-то вроде:
EXEC ('SELECT @ActualNumberOfResults = COUNT(*) ' + @sqlBody)
Я вижу, почему это не работает, и почему возникает ошибка, которая не объявлена, но я думаю, что она адекватно описывает то, что я пытаюсь выполнить.
Любые идеи?
Ответы
Ответ 1
Вместо этого вместо динамического запроса можно вернуть результат в виде набора строк, который вы затем вставляете в переменную таблицы (также может быть временной или обычной таблицей) с использованием синтаксиса INSERT ... EXEC
. После этого вы можете просто прочитать сохраненное значение в переменной с помощью SELECT @var = ...
:
DECLARE @rowcount TABLE (Value int);
INSERT INTO @rowcount
EXEC('SELECT COUNT(*) ' + @sqlBody);
SELECT @ActualNumberOfResults = Value FROM @rowcount;
Ответ 2
Поздно в день, но я нашел этот метод намного проще:
-- test setup
DECLARE @sqlBody nvarchar(max) = N'SELECT MyField FROM dbo.MyTable WHERE MyOtherField = ''x''';
DECLARE @ActualNumberOfResults int;
-- the goods
EXEC sp_executesql @sqlBody;
SET @ActualNumberOfResults = @@ROWCOUNT;
SELECT @ActualNumberOfResults;
Ответ 3
используйте sp_executesql
и output parameter
Пример
DECLARE @sqlBody VARCHAR(500),@TableCount INT, @SQL NVARCHAR(1000)
SELECT @sqlBody = 'from sysobjects'
SELECT @SQL = N'SELECT @TableCount = COUNT(*) ' + @sqlBody
EXEC sp_executesql @SQL, N'@TableCount INT OUTPUT', @TableCount OUTPUT
SELECT @TableCount
GO
Ответ 4
После выполнения вашего фактического запроса сохраните результат @@ROWCOUNT
в любой переменной, которую вы можете использовать позже.
EXEC sp_executesql 'SELECT TOP 10 FROM ABX'
SET @TotRecord = @@ROWCOUNT
в вашу переменную для последующего использования.
Ответ 5
Имейте в виду, что динамический SQL имеет свою собственную область. Любая переменная, объявленная/измененная там, выйдет за пределы области действия после вашего EXEC
или вашего sp_executesql
.
Предложите писать в временную таблицу, которая будет в пределах вашего динамического оператора SQL и снаружи.
Возможно, поместите его в свой sqlBottom
:
CREATE TABLE ##tempCounter(MyNum int);
EXEC('SELECT @ActualNumberOfResults = COUNT(*) ' + @sqlBody +
'; INSERT INTO ##tempCounter(MyNum) VALUES(@ActualNumberOfResults);');
SELECT MyNum FROM ##tempCounter;
Ответ 6
Единственная проблема с ответами на создание временных таблиц (использование "DECLARE @rowcount TABLE" или "CREATE TABLE ## tempCounter (MyNum int)" ) заключается в том, что вам нужно прочитать все затронутые записи с диска в Память. Если вы ожидаете большого количества записей, это может занять некоторое время.
Таким образом, если ответ, вероятно, будет большим, решение "use sp_executesql и выходного параметра" является более эффективным ответом. И он действительно работает.