Вероятность синтаксиса: использование переменных в хранимых процедурах
Может ли кто-нибудь сказать, насколько правильный следующий код ниже. Я пытаюсь создать хранимую процедуру, которая возвращает количество строк таблицы, имя которой ей передано.
CREATE PROCEDURE spROWCOUNTER
(
@tablename nvarchar(20)
@rowCountVal int OUTPUT
)
AS
DECLARE @strQuery nvarchar(300)
SET @strQuery = 'SELECT @rowCountVal=COUNT(*) FROM '+@tablename
EXEC(@strQuery)
RETURN @rowCountVal
СООБЩЕНИЕ ОБ ОШИБКЕ :
- Неправильный синтаксис рядом с @rowCountVal
- Необходимо объявить скалярную переменную @tablename
- Необходимо объявить скалярную переменную @rowCountVal
тогда как код ниже работает нормально
ALTER PROCEDURE spROWCOUNTER
(
@rowCountVal int OUTPUT
)
AS
SELECT @rowCountVal=COUNT(*) FROM DEFECT_LOG
RETURN @rowCountVal
Ответы 6
Синтаксическую проблему решить несложно. Между вашими параметрами есть пропущенная запятая (","). Вставьте запятую, и хранимая процедура компилируется:
( @tablename nvarchar(20), @rowCountVal int OUTPUT )
Затем есть серьезная проблема: вы не можете получить доступ к параметру @rowCountVal внутри оператора EXEC. Чтобы решить эту проблему, вы можете использовать встроенную хранимую процедуру sp_executesql.
Прочтите это хорошая статья, написанная MVP SQL Server Эрландом Соммарскогом.
Кстати: вам не нужно «возвращать» переменную. Возвращаемые значения обычно используются для возврата некоторых значений статуса. Если вы передадите выходной параметр, он будет автоматически возвращен.
да, я пропустил запятую. Но даже после этого значение не сохраняется в @rowCountVal.
SET @strQuery = 'SELECT @rowCountVal=COUNT(*) *FROM '+ @tablename
EXEC(@strQuery)
запрос не возвращает и не отображает никакого значения.
Кстати, я думал вызвать эту процедуру из других хранимых процедур, чтобы получить rowcounts.
Будет ли работать следующее утверждение:
set @rCount = exec spROWCOUNTER('DEFECT_LOG')
CREATE PROCEDURE spROWCOUNTER
@tablename nvarchar(20),
@rowCountVal int OUTPUT
AS
SELECT @rowCountVal = ISNULL(SUM(spart.rows), 0)
FROM sys.partitions spart
WHERE spart.object_id = object_id(@tablename) AND spart.index_id < 2
RETURN @rowCountVal
Я бы использовал sp_executesql вместо exec. Затем вы можете передать @rowCountVal в качестве выходной переменной в динамический sql.
create PROCEDURE spROWCOUNTER
(
@tablename nvarchar(20),
@rowCountVal int OUTPUT
)
AS
DECLARE @strQuery nvarchar(300)
SET @strQuery = 'SELECT @rowCountVal = COUNT(*) FROM '+@tablename
exec sp_executesql @strQuery, N'@tablename nvarchar(20), @rowCountVal int OUTPUT', @tablename = @tablename, @rowCountVal = @rowCountVal output
RETURN @rowCountVal
В операторе execute вы можете использовать временную таблицу для обмена данными:
CREATE PROCEDURE spROWCOUNTER
@tablename nvarchar(20),
@rowCountVal int OUTPUT
AS
CREATE TABLE #Result( Rows INT )
EXEC( 'INSERT INTO #Result( Rows ) SELECT COUNT(*) FROM ' + @tablename )
SELECT @rowCountVal = Rows FROM #Result
RETURN @rowCountVal
если вы хотите, чтобы количество строк было функцией, вы также можете проверить Повышение производительности подсчета таблиц в SQL Server 2005
Представленная в статье функция, по-видимому, быстрее, чем вызов count (*) для очень больших таблиц.