Today, I have provided an article showing you how to display the return value from a Stored Procedure output parameter in a Query Window 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. You can determine the value of the out parameter corresponding to the different condition as an out parameter. Let's have a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Output Keyword

The Output keyword to the parameters in the Stored Procedures can return the values of the parameters to the calling program. 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:

Sql-server-Table.jpg

Now create a Stored Procedure:

  1. Create PROCEDURE [dbo].[ExecuteProcedurewithOUTParameter]
  2. (
  3. @UserName VARCHAR(100),
  4. @ResultValue int out
  5. )
  6. AS
  7. BEGIN TRAN
  8. IF EXISTS
  9. (
  10. SELECT * FROM Testoutvalue
  11. WHERE Name = @UserName
  12. )
  13. BEGIN
  14. SET @ResultValue = -5
  15. END
  16. ELSE
  17. BEGIN
  18. INSERT INTO Testoutvalue
  19. (
  20. Name
  21. )
  22. VALUES
  23. (
  24. @UserName
  25. )
  26. set @ResultValue = 1
  27. END
  28. IF @@ERROR <> 0
  29. BEGIN
  30. ROLLBACK TRAN
  31. END
  32. ELSE
  33. BEGIN
  34. COMMIT TRAN
  35. END
  36. Select @ResultValue
In the preceding Stored Procedure we declare an output parameter 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 1.

Now hit F5 to execute the Stored Procedure:

SQL-server.jpg

Now insert a value into the table using the Stored Procedure and check the result of executing the Stored Procedure with the output parameter values as follows:

  1. DECLARE @Outputparam VARCHAR(20);
  2. Exec ExecuteProcedurewithOUTParameter 'ROhatashkumar', @Outputparam output
Now execute the Stored Procedure. Now press F5 to run the Stored Procedure.

Stored-Procedure-with-Outparameter-in-SQLServer.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.

Table-in-SQLServer.jpg

Now, we can execute the procedure with duplicate values to check how the RETURN statement works:

  1. DECLARE @Outputparam VARCHAR(20);
  2. Exec ExecuteProcedurewithOUTParameter 'Rahul',@Outputparam output
Output

SQLServer-Stored-Procedure-with-Outparameter.jpg