Stored procedures can be used to return values. Stored procedures can return int, char, varchar etc., data type values. But, then can not return table variables.
Syntax for the stored procedures with input and output parameters is:
CREATE PROC <Procedure Name>
@Input_Var <Data Type>,
@Output_Var <Data Type> OUTPUT
AS
BEGIN
<T-SQL Commands>
END
Note: Any number of input and output commands can be passed to the stored procedure till it reaches maximum parameters limit.
Example: Let’s create a stored procedure to find out to which class a student belongs to if student id is provided.
In this scenario student Id will be an input parameter and class will be output parameter.
Stored procedure:
CREATE PROC USP_CLASS_OF_STUD
@STUDENT_ID INT,
@CLASS_ID INT OUTPUT
AS
BEGIN
SELECT @CLASS_ID = CLASS_ID
FROM STUDENT_CLASS
WHERE STUDENT_ID =@STUDENT_ID
RETURN @CLASS_ID
END
Execute the above stored procedure to find out the class of a student whose id is 11.
DECLARE @CLASS INT
EXEC USP_CLASS_OF_STUD 11, @CLASS OUTPUT
SELECT @CLASS
