Example for creating a stored procedure with an input parameter

Posted on Monday, 13 August 2012 by SQL Developer

Example 1:

Create a stored procedure Student id, firstname and lastname from STUDENT table by joining the STUDENT_CLASS table Where class = @Class_Id_parameter

Query:


CREATE PROC USP_STUDENTS_IN_CLASS
@CLASSS_ID  INT
AS
BEGIN
      --Select the student deatils whose class id is @CLASSS_ID
      SELECT
            S.STUDENT_ID,
            S.FIRST_NAME,
            S.LAST_NAME
      FROM STUDENT      S
      INNER JOIN STUDENT_CLASS      SC
            ON    S.STUDENT_ID =SC.STUDENT_ID
            AND SC.CLASS_ID= @CLASSS_ID

END


Example 2:  Execute the above stored procedure to fetch 10th class student's information/

Query:

EXEC USP_STUDENTS_IN_CLASS 10




This entry was posted in . Bookmark the permalink.

Leave a reply