A tutorial on passing parameter values back out from a stored procedure

 
In the given previous topic we observed how to pass parameters into a stored procedure; however, another way is to pass parameter values back out from a stored procedure.
 
One way of doing this is that you call another stored procedure that does not return any data, however returns parameter values cab be used by the calling stored procedure.

Explanation

 
Setting up output parameters for a stored procedure is fundamentally the same as setting up input parameters, the only variation is that you use the OUTPUT clause after the parameter name to mention that it must return a value.
 
The output clause may be specified by either using the keyword “OUTPUT” or just “OUT”. For examples we are still using the AdventureWorks database, such that all the stored procedures must be created in the AdventureWorks database.

Simple Output

 
CREATE PROCEDURE dbo.uspGetAddressCount @City nvarchar(30), @AddressCount int OUTPUT
AS
SELECT @AddressCount = count(*)
FROM AdventureWorks.Person.Address
WHERE City = @City
 
Or it can be performed in this way:
 
CREATE PROCEDURE dbo.uspGetAddressCount @City nvarchar(30), @AddressCount int OUT
AS
SELECT @AddressCount = count(*)
FROM AdventureWorks.Person.Address
WHERE City = @City
 
To call this above mention stored procedure we can execute it as follows.
 
Step 1: Declare a variable,
Step 2: Execute the stored procedure and
Step 3: Now select the returned valued.
 
DECLARE @AddressCount int
EXEC dbo.uspGetAddressCount @City = ‘Calgary’, @AddressCount = @AddressCount OUTPUT
SELECT @AddressCount
 
This can also be performed as follows, where the stored procedure parameter names are not passed as an argument.
 
DECLARE @AddressCount int
EXEC dbo.uspGetAddressCount ‘Calgary’, @AddressCount OUTPUT
SELECT @AddressCount

  • A new option which was added in SQL Server 2005 was the capability to use the Try..Catch paradigm which exists in other development languages to handle exceptions.
  • Performing error handling in SQL Server has not always been the easy approach, so this option definitely makes it much simple to code and handle errors.

Explanation

 
The Try…Catch paradigm is basically two blocks of code containing your stored procedures that allow you execute some code, this is the Try section and in case there are errors they are handled in the Catch section.
 
For example, you will observe that we have used a basic SELECT statement that is contained within the TRY section, however, for some reason if this fails it is going to run the code in the CATCH section and return the error related information.
 
CREATE PROCEDURE dbo.uspTryCatchTest
AS
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Kristin is a content strategist at Techarex Networks. Kristin follows the B2B technology space closely and loves to write on the latest changes in technology, futuretech and fixes for day to day how to issues. Besides writing Kristin also loves music, moves and skating.