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

Question

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.

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?

-- 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'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.

Answer 2

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