I'm writing a stored procedure and I want to return 0 records when something fails. I can't seem to figure out how to just return 0 rows? I've used SELECT NULL
but this returns 1 row with a NULL
in row 1 col 1. I have also tried not specifying any SELECT
statements in my error code path but when testing the value of @@ROWCOUNT
after the call to the SP, it returned 1. I think this may be because the @@ROWCOUNT
was never reset from the SELECT
statement earlier in the SP (in the EXISTS()
). Any advice would be appreciated.
我正在写一个存储过程,我想在出现故障时返回0条记录。我似乎无法弄清楚如何只返回0行?我已经使用了SELECT NULL但是这会在第1行col 1中返回1行,并且在我的错误代码路径中没有指定任何SELECT语句,但是在调用SP后测试@@ ROWCOUNT的值时,返回1.我认为这可能是因为@@ ROWCOUNT从未在SP之前的SELECT语句中重置(在EXISTS()中)。任何意见,将不胜感激。
Also, I've got XACT_ABORT
set to ON, but I have also used a TRY/CATCH
block to ensure I return the correct error "return value" from the stored procedure. Is this okay? If there is an error, will the XACT_ABORT
override the TRY/CATCH
or will my error code path still lead to the correct return values being returned?
此外,我已将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 个解决方案
#1
12
To return 0 rows, you can do:
要返回0行,您可以执行以下操作:
SELECT TOP 0 NULL AS MyValue
Personally, I'd use an OUTPUT parameter for this sproc to return the ID back out instead of returning a resultset - that's just my preference though. Then just set that output parameter to e.g. -1 as default to indicate nothing done.
就个人而言,我会使用这个sproc的OUTPUT参数来返回ID而不是返回结果集 - 这只是我的偏好。然后将该输出参数设置为例如默认为-1表示没有完成。
#2
1
this is how I'd do it:
这是我怎么做的:
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
#1
12
To return 0 rows, you can do:
要返回0行,您可以执行以下操作:
SELECT TOP 0 NULL AS MyValue
Personally, I'd use an OUTPUT parameter for this sproc to return the ID back out instead of returning a resultset - that's just my preference though. Then just set that output parameter to e.g. -1 as default to indicate nothing done.
就个人而言,我会使用这个sproc的OUTPUT参数来返回ID而不是返回结果集 - 这只是我的偏好。然后将该输出参数设置为例如默认为-1表示没有完成。
#2
1
this is how I'd do it:
这是我怎么做的:
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