Stored procedure output parameters or return values - Part 20

In this video, we will
1. Understand what are stored procedure return values
2. Difference between stored procedure return values and output parameters
3. When to use output parameters over return values

Before watching this video, please watch
Part 18 - Stored procedure basics in sql server
Part 19 - Stored procedures with output parameters







What are stored procedure status variables?
Whenever, you execute a stored procedure, it returns an integer status variable. Usually, zero indicates success, and non-zero indicates failure. To see this yourself, execute any stored procedure from the object explorer, in sql server management studio. 
1. Right Click and select 'Execute Stored Procedure
2. If the procedure, expects parameters, provide the values and click OK.
3. Along with the result that you expect, the stored procedure, also returns a Return Value = 0

So, from this we understood that, when a stored procedure is executed, it returns an integer status variable. With this in mind, let's understand the difference between output parameters and RETURN values. We will use the Employees table below for this purpose.




The following procedure returns total number of employees in the Employees table, using output parameter - @TotalCount.
Create Procedure spGetTotalCountOfEmployees1
@TotalCount int output
as
Begin
Select @TotalCount = COUNT(ID) from tblEmployee
End

Executing spGetTotalCountOfEmployees1 returns 3.
Declare @TotalEmployees int
Execute spGetTotalCountOfEmployees @TotalEmployees Output
Select @TotalEmployees

Re-written stored procedure using return variables
Create Procedure spGetTotalCountOfEmployees2
as
Begin
return (Select COUNT(ID) from Employees)
End

Executing spGetTotalCountOfEmployees2 returns 3.
Declare @TotalEmployees int
Execute @TotalEmployees = spGetTotalCountOfEmployees2
Select @TotalEmployees

So, we are able to achieve what we want, using output parameters as well as return values. Now, let's look at example, where return status variables cannot be used, but Output parameters can be used.

In this SP, we are retrieving the Name of the employee, based on their Id, using the output parameter @Name.
Create Procedure spGetNameById1
@Id int,
@Name nvarchar(20) Output
as
Begin
Select @Name = Name from tblEmployee Where Id = @Id
End

Executing spGetNameById1, prints the name of the employee
Declare @EmployeeName nvarchar(20)
Execute spGetNameById1 3, @EmployeeName out
Print 'Name of the Employee = ' + @EmployeeName

Now let's try to achieve the same thing, using return status variables.
Create Procedure spGetNameById2
@Id int
as
Begin
Return (Select Name from tblEmployee Where Id = @Id)
End

Executing spGetNameById2 returns an error stating 'Conversion failed when converting the nvarchar value 'Sam' to data type int.'. The return status variable is an integer, and hence, when we select Name of an employee and try to return that we get a converion error. 

Declare @EmployeeName nvarchar(20)
Execute @EmployeeName = spGetNameById2 1
Print 'Name of the Employee = ' + @EmployeeName

So, using return values, we can only return integers, and that too, only one integer. It is not possible, to return more than one value using return values, where as output parameters, can return any datatype and an sp can have more than one output parameters. I always prefer, using output parameters, over RETURN values.

In general, RETURN values are used to indicate success or failure of stored procedure, especially when we are dealing with nested stored procedures.Return a value of 0, indicates success, and any nonzero value indicates failure.

Difference between return values and output parameters

15 comments:

  1. Declare @TotalEmployees int
    Execute spGetTotalCountOfEmployees @TotalEmployees Output
    Select @TotalEmployees

    Store procedure name is wrong here.It should show "spGetTotalCountOfEmployees1" instead of "spGetTotalCountOfEmployees"

    ReplyDelete
    Replies
    1. what you are trying to ask i am not under sand...!as per my analysis you create a procedure with "spGetTotalCountOfEmployees1" this name.if you want Rename the procedure "sp_rename "

      Delete
    2. spGetTotalCountOfEmployees1 is a stored procedure name used to demonstrate the usage of Output variable..
      spGetTotalCountOfEmployees2 is a stored procedure name used to demonstrate the usage of Return value ..

      Delete
    3. He said there is a typo in the description... it should be "spGetTotalCountOfEmployees1" instead of "spGetTotalCountOfEmployees" in the execution!

      Delete
  2. Hi All,
    Venket you are doing a great job, may God bless you.

    One question: - "Whenever, you execute a stored procedure, it returns an integer status variable. Usually, zero indicates success, and non-zero indicates failure. "

    When we execute "spGetTotalCountOfEmployees2" it returns 3

    Why doesn't this mean that execution is failed?

    ReplyDelete
    Replies
    1. Here the status is not 3 .. the count of employees is 3.. but the status is 0..

      Delete
  3. can u say something about execute @RC storeproc

    ReplyDelete
  4. Effected Row are shown on status by query...

    ReplyDelete
  5. Hi venket
    hope doing good actually i have a doubt about output parameters
    Create Procedure spGetTotalCountOfEmployees1
    @TotalCount int output
    as
    Begin
    Select @TotalCount = COUNT(ID) from tblEmployee
    End
    in this query in body section you wrote select @totalcount=count(id) why dont we use directly select count(id)
    i tried to execute it i got the output but statues its showing NULL and then again i did small change in sp and executed its showing in massage box ecach row exiecution and finally error came
    the error "Msg 217, Level 16, State 1, Procedure spcountofemployes, Line 9 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)." please let me know the problem how it executed in backend of sql when i use @totalcount and with out it
    tahnks

    ReplyDelete
    Replies
    1. Because you have to set the output variable value @TotalCount = COUNT(ID). Otherwise output variable may not have anything

      Delete
  6. Hi
    Thanks a lot for your work.
    I have question...When exactly do we use stored procedures with output parameters and when do we use stored procedures without parameters?

    I base my question on an example:

    Stored procedure with output parameter

    CREATE PROCEDURE uspGetContactsCountByCity
    @City nvarchar(60),
    @ContactsCount int OUT
    AS
    BEGIN
    SELECT @ContactsCount = COUNT(ContactID)
    FROM Contacts
    WHERE City = @City
    END
    Stored procedure executing

    DECLARE @ContactsTotal INT

    EXEC uspGetContactsCountByCity @ContactsCount = @ContactsTotal OUT, @city = 'Berlin'
    SELECT @ContactsTotal
    Results: 2

    Stored procedure without output parameter

    CREATE PROCEDURE uspGetContactsCountByCity2
    @City nvarchar(60)
    AS
    BEGIN
    SELECT COUNT(ContactID)
    FROM Contacts
    WHERE City = @City
    END
    Stored procedure executing:

    EXEC uspGetContactsCountByCity2 @city = 'Berlin'
    Results: 2

    Both procedures return the same result, in same form, so what's the difference?

    ReplyDelete
  7. Hi Venkat,
    Not only you are very very good with SQL, but also you are a very very good teacher as well. Thanks for sharing your knowledge!

    ReplyDelete
  8. Begin
    Select @TotalCount = COUNT(ID) from tblEmployee
    End

    Executing spGetTotalCountOfEmployees1 returns 3.

    How its return 3 if count(ID) is more than that?

    ReplyDelete
  9. Executing spGetTotalCountOfEmployees1 returns 3.

    How its return 3 if count(ID) is more than that?

    ReplyDelete
  10. Venkat you Rock. Very informative, helpful lesson and that to very organised. Thank you so much for this hard work. God bless you.

    ReplyDelete

It would be great if you can help share these free resources