CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008stored-proceduresreturn
thevan
thevan

Reputation: 10354

Problem due to using RETURN in Stored Procedure

I have written a stored procedure. In it I want to check a condition, and if that condition turns true then I don't want to proceed for the remaining part of the stored procedure.

If that returns false, then I want to continue the remaining part of the stored procedure.

My stored procedure is here:

    IF (@RefPOID <> 0)
    BEGIN
    IF EXISTS(// SELECT STATEMENT)
    BEGIN
    SET @Status = 'Please select the Items'                 
        RETURN
    END 
END 
    IF(POID = 0)
    BEGIN

           //INSERTION

    END
    ELSE
    BEGIN

           //DELETION

    END

This stored procedure had compiled successfully. But while calling this stored procedure from the front end, it shows the error

System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded.

If I removed the RETURN statement from the stored procedure, then its working fine.

If the condition turns false, the stored procedure is working fine when I call from the front end.

But I want to check the condition there and to return if it is true.

I am sure problem arise due to the presence of RETURN.

Why the problem arise? What had gone wrong? How to solve this? Any suggestion please.

Upvotes: 0

Views: 474

Answers (2)

Mr Moose
Mr Moose

Reputation: 6344

If you are intending for the status to indicate a failure, then I'd be tempted to either perform the validation check prior to calling this stored proc or use RAISERROR within a TRY CATCH block. 

BEGIN TRY

IF (@RefPOID <> 0)
BEGIN
      IF EXISTS(SELECT 1 FROM @Item I WHERE NOT EXISTS(SELECT P.ItemID FROM PUR_POIndent P WHERE P.ItemID = I.ItemID AND P.D1 = I.D1 AND P.D2 = I.D2 AND P.D3 = I.D3 AND P.POID = @RefPOID))
          BEGIN
          RAISERROR('Please select Items only related to the Reference Purchase', 16, 1)
          RETURN
      END  
END

...
END TRY
BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(MAX);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                        );
END CATCH

Upvotes: 1

kheya
kheya

Reputation: 7621

I don't think your return is the problem. The query might be invalid. But you said it works if you remove the return. That is strange.

Could you please post the complete code?

Have you tried to run in in query analyzer or studio manager?

You have:

SELECT 1 FROM @Item

Is this valid? What is @Item - a table variable?

Upvotes: 1

Related Questions