Stored Procedure With a Return Value in SQL Server 2012

Today, I have provided an article showing you how to use a return value with a Stored Procedure in SQL Server 2012. In this article, we create a stored procedure to avoid duplicate record insertion in the SQL Server database prior to insertion into the database. If we insert a duplicate record in the table then execution of a stored procedure will return a status value. If we insert a record into the table which is not duplicates. The successful execution of a stored procedure will return 0. Let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
 

Return Value in Stored Procedure

 
Return values can be used within stored procedures to provide the stored procedure execution status to the calling program. You can create your own parameters that can be passed back to the calling program. By default, the successful execution of a stored procedure will return 0.
 
Now create a table named UserTable with the columns UserID, UserName. Set the identity property=true for UserID. The table looks as in the following:
 
img4.jpg
 
Now create a stored procedure:
  1. Create PROCEDURE UsingExistsstoredprocedure  
  2. (  
  3.       @UserName VARCHAR(100)  
  4. )  
  5. AS  
  6. DECLARE @ResultValue int  
  7. BEGIN TRAN  
  8. IF EXISTS  
  9.     (  
  10.           SELECT * FROM UserTable  
  11.           WHERE UserName = @UserName  
  12.         )  
  13.      BEGIN  
  14.          SET  @ResultValue = -5  
  15.      END  
  16. ELSE  
  17.       BEGIN  
  18.            INSERT INTO UserTable  
  19.                (  
  20.                    UserName   
  21.                )  
  22.            VALUES  
  23.            (  
  24.                  @UserName  
  25.            )  
  26.            set @ResultValue = @@ERROR  
  27.      END  
  28. IF @ResultValue <> 0  
  29.      BEGIN  
  30.             ROLLBACK TRAN  
  31.       END  
  32. ELSE  
  33.       BEGIN  
  34.             COMMIT TRAN  
  35.       END  
  36. RETURN @ResultValue  
In the preceding stored procedure we declare a return variable ResultValue. If exists is used to check the insertion record whether it belongs to the table or not. If the inserted record is already in the table then set the return status @ResultValue = -5 and the inserted record is not in the table by default; the successful execution of a stored procedure will return 0. The syntax of the return command is:
RETURN integer_value
 
Now hit F5 to execute the stored procedure:
 
execute stored procedure
 
Now insert a value into the table using the stored procedure and check the result of executing the stored procedure with the return values as follows:
 
EXEC @return_variable = stored_procedure_name
 
Now execute the stored procedure:
  1. DECLARE  @return_status int  
  2. EXEC @return_status= UsingExistsstoredprocedure 'Rohatash'  
  3. SELECT @return_status;  
Now press F5 to run the stored procedure.
 
img3.jpg
 
Every time you insert new records it will return the same above output. Now using a select statement to select the record from the table.
 
img5.jpg
 
Now, we can execute the procedure with duplicate values to check how the RETURN statement works:
  1. DECLARE  @return_status int  
  2. EXEC @return_status= UsingExistsstoredprocedure 'Rohatash'  
  3. SELECT @return_status;  
Output
 
img6.jpg