Input parameters can be passed to stored procedures as and when required. Here is the syntax for the stored procedure with input parameters and without output parameters.
Syntax:
CREATE PROC <Procedure Name>
@Input1 <Data type>,
@Input2 <Data type>,
@InputN <Data type>,
AS
BEGIN
<T-SQL commands>
END
Here is an example for the stored procedure with input parameters. In this Scenario, I would like to know the students who are in a particular class.
For this case, we will be passing Class Id as input parameter.
Stored procedure for the above scenario is:
Stored Procedure:
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
Query to fetch the students who are in 5th class, following query can be executed. That is following query will be used to run the above stored procedure with the input parameter 5.
EXEC USP_STUDENTS_IN_CLASS 5
