What will stored procedure return

So, from this point, we understood that, when a stored procedure is executed, it returns an integer status variable. Create a procedure that will count the total number of employees in the Employee table using the output parameter. When we execute the above SP it returns 6.

Create a procedure that will count the total number of employees in the Employee table using return status. When we execute the above SP, it also returns 6. There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes. This topic provides information on the three approaches. For large result sets, the stored procedure execution will not continue to the next statement until the result set has been completely sent to the client.

For small result sets, the results will be spooled for return to the client and execution will continue. If multiple such SELECT statements are run during the execution of the stored procedure, multiple result sets will be sent to the client. If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the procedure.

The following example shows a procedure with an input and an output parameter. The SalesPerson parameter would receive an input value specified by the calling program. The following example calls the procedure created in the first example and saves the output value returned from the called procedure in the SalesYTD variable, which is local to the calling program.

This allows the procedure to receive a value from the calling program, change or perform operations with the value, and then return the new value to the calling program. Using Return keyword in Stored Procedure. The following Stored Procedure accepts EmployeeId as parameter.

It checks whether an Employee with the supplied EmployeeId exists in the Employees table of the Northwind database. You cannot use it for returning values of any other data types. If the Employee exists it returns value 1 and if the EmployeeId is not valid then it returns 0. EmployeeId INT.

FROM Employees. In order to fetch the returned integer value from the Stored Procedure, you need to make use of an Integer variable and use along with the EXEC command while executing the Stored Procedure. Valid EmployeeId. Invalid EmployeeId. Collectives on Stack Overflow. Learn more. How to return the output of stored procedure into a variable in sql server Ask Question. Asked 9 years, 3 months ago. Active 3 years ago. Viewed k times. Improve this question. Adham Adham 60k 96 96 gold badges silver badges bronze badges.

What does your stored procedure return? A single value? A result set? Add a comment. Active Oldest Votes.