A parameter can be passed to stored procedure to perform a task. Syntax:
USE Pubs
GO
CREATE PROCEDURE | PROC _spName _varName AS varType (size)
AS
_SQLQuery;
Parameter can be supplied to Stored Procedure as follows while calling it:
Stored Procedure with parameter example:
USE Pubs
GO
//Create a SP with parameter type of varchar to supply first name of employee
to return matching record///
CREATE PROCEDURE SP_findGivenNameEmployeeRecord
@spparamvar_firstName AS VARCHAR (10 )
AS
SELECT * FROM employee WHERE fname LIKE @spparamvar_firstName;
EXEC SP_findGivenNameEmployeeRecord 'helen';
emp_id fname minit lname
--------- -------------------- -----
H-B39728F Helen Bennett
(1 row(s) affected)