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 Procedure2. 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 = 0So, 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 outputasBeginSelect @TotalCount = COUNT(ID) from tblEmployeeEndExecuting spGetTotalCountOfEmployees1 returns 3.Declare @TotalEmployees intExecute spGetTotalCountOfEmployees @TotalEmployees OutputSelect @TotalEmployeesRe-written stored procedure using return variablesCreate Procedure spGetTotalCountOfEmployees2asBeginreturn (Select COUNT(ID) from Employees)EndExecuting spGetTotalCountOfEmployees2 returns 3.Declare @TotalEmployees intExecute @TotalEmployees = spGetTotalCountOfEmployees2Select @TotalEmployeesSo, 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) OutputasBeginSelect @Name = Name from tblEmployee Where Id = @IdEndExecuting spGetNameById1, prints the name of the employeeDeclare @EmployeeName nvarchar(20)Execute spGetNameById1 3, @EmployeeName outPrint 'Name of the Employee = ' + @EmployeeNameNow let's try to achieve the same thing, using return status variables.Create Procedure spGetNameById2@Id intasBeginReturn (Select Name from tblEmployee Where Id = @Id)EndExecuting 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 1Print'Name of the Employee = ' + @EmployeeNameSo, 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.

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 ..

Hi venkethope doing good actually i have a doubt about output parameters Create Procedure spGetTotalCountOfEmployees1@TotalCount int outputasBegin Select @TotalCount = COUNT(ID) from tblEmployeeEndin 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 ittahnks