T-SQL: как вернуть 0 строк из хранимой процедуры и как использовать XACT_ABORT и TRY / CATCH
Я пишу хранимую процедуру и хочу вернуть 0 записей, когда что-то не получается. Я не могу понять, как просто вернуть 0 строк? Я использовал SELECT NULL , но это возвращает 1 строку с NULL в строке 1, столбец 1. Я также пытался не указывать какие-либо операторы SELECT в моем пути кода ошибки, но при тестировании значения @@ ROWCOUNT после вызова SP он вернул 1. Я думаю, что это может потому что @@ ROWCOUNT никогда не сбрасывался из оператора SELECT ранее в SP (в EXISTS () ). Буду признателен за любой совет.
Кроме того, у меня XACT_ABORT установлено значение ON, но я также использовал блок TRY / CATCH , чтобы гарантировать, что я верну правильную ошибку " возвращаемое значение »из хранимой процедуры. Это нормально? Если есть ошибка, будет ли XACT_ABORT переопределять TRY / CATCH или мой путь к коду ошибки все еще приведет к возвращению правильных возвращаемых значений?
-- Setup
SET NOCOUNT ON; -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET XACT_ABORT ON; -- SET XACT_ABORT ON rollback transactions on errors
DECLARE @return int; SET @return = 1; -- Default to general error
-- Start transaction
BEGIN TRANSACTION
BEGIN TRY
IF NOT EXISTS(SELECT NULL FROM [MyTable] WHERE [Check] = 1)
BEGIN
-- Insert new record
INSERT INTO [MyTable] (Check, Date) VALUES (1, GETDATE());
SELECT SCOPE_IDENTITY() AS [MyValue]; -- Return 1 row
SET @return = 0; -- Success
END
ELSE
BEGIN
-- Fail
SELECT NULL AS [MyValue]; -- Want to return 0 rows not 1 row with NULL
SET @return = 2; -- Fail error
END
END TRY
BEGIN CATCH
-- Error
ROLLBACK TRANSACTION;
SELECT NULL AS [MyValue]; -- Want to return 0 rows not 1 row with NULL
SET @return = 1; -- General error
END CATCH
-- End transaction and return
COMMIT TRANSACTION
RETURN @return;
2 ответа
Чтобы вернуть 0 строк, вы можете:
SELECT TOP 0 NULL AS MyValue
Лично я бы использовал параметр OUTPUT для этого sproc, чтобы вернуть идентификатор обратно вместо возврата набора результатов - хотя это только мое предпочтение. Затем просто установите этот выходной параметр, например, -1 по умолчанию, чтобы указать, что ничего не сделано.
вот как я это сделаю:
CREATE PROCEDURE YourProcedure
AS
( @NewMyValue int OUTPUT --<<<<<use output parameter and not a result set
)
BEGIN TRY
--<<<<put everything in the BEGIN TRY!!!
-- Setup
SET NOCOUNT ON; -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET XACT_ABORT ON; -- SET XACT_ABORT ON rollback transactions on errors
DECLARE @return int
--<<init multiple variables in a select, it is faster than multiple SETs
--set defaults
SELECT @return = 1 -- Default to general error
,@NewMyValue=NULL
-- Start transaction
BEGIN TRANSACTION --<<<put the transaction in the BEGIN TRY
--<<<lock rows for this transaction using UPDLOCK & HOLDLOCK hints
IF NOT EXISTS(SELECT NULL FROM [MyTable] WITH (UPDLOCK, HOLDLOCK) WHERE [Check] = 1)
BEGIN
-- Insert new record
INSERT INTO [MyTable] (Check, Date) VALUES (1, GETDATE());
SELECT @NewMyValue=SCOPE_IDENTITY() --<<<set output parameter, no result set
,@return = 0; -- Success
END
ELSE
BEGIN
-- Fail
--<<no need for a result set!!! output parameter was set to a default of NULL
SET @return = 2; -- Fail error
END
COMMIT TRANSACTION --<<<commit in the BEGIN TRY!!!
END TRY
BEGIN CATCH
-- Error
IF XACT_STATE()!=0 --<<<only rollback if there is a bad transaction
BEGIN
ROLLBACK TRANSACTION
END
--<<any insert(s) into log tables, etc
--<<no need for a result set!!! output parameter was set to a default of NULL
SET @return = 1; -- General error
END CATCH
-- End transaction and return
RETURN @return;
GO