T-SQL: how to return 0 rows from a stored procedure and how to use XACT_ABORT and TRY / CATCH

Question

I am writing a stored procedure and I want to return 0 records when something fails. I can't figure out how to simply return 0 rows? I used SELECT NULL , but this returns 1 row with NULL in row 1 col 1. I also tried not to specify any SELECT in my error code path, but when testing the @@ROWCOUNT after calling SP, it returned 1. I I think it could be because @@ROWCOUNT has never been reset from a SELECT earlier in SP (in EXISTS() ). Any advice would be appreciated.

In addition, I have XACT_ABORT , but I also used the TRY/CATCH block to ensure that the return value of the error is returned from the stored procedure. This is normal? If an error occurs, will XACT_ABORT override TRY/CATCH or will my error code path still return return values?

 -- 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; 

Answer 1

To return 0 rows, you can do:

 SELECT TOP 0 NULL AS MyValue 

Personally, I would use the OUTPUT parameter for this sproc to return the identifier back instead of returning a result set - this is just my preference. Then just set this output parameter, for example. -1 by default to indicate that nothing has been done.

Answer 2

here's how i 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