Example for Creating a stored procedure with input and output parameteres

Example 1:

Create a stored procedure to find out the class of a student based on the student id.

Here, Student ID is an input parameter and Class ID is a output parameter.


Query:

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

Example 2:

Execute the above stored procedure to find of the class of a student whose id is 5

Query:

DECLARE @CLASS INT
EXEC USP_CLASS_OF_STUD 5, @CLASS OUTPUT
SELECT @CLASS


This entry was posted in . Bookmark the permalink.

Leave a reply