Creating a stored procedure that accepts parameters involves using the SQL Data Definition Language (DDL) practices specific to the particular database management system (DBMS) you are using, such as MySQL, SQL Server, Oracle, or PostgreSQL. Below, I'll provide examples for MySQL and SQL Server to illustrate how to create a stored procedure that accepts parameters.
Example in MySQL
In MySQL, you can create a stored procedure using the CREATE PROCEDURE statement. Here's a simple example:
DELIMITER //
CREATE PROCEDURE GetEmployeeById(IN empId INT)
BEGIN
SELECT * FROM Employees WHERE EmployeeID = empId;
END //
DELIMITER ;
In this example:
- GetEmployeeById is the name of the stored procedure.
- IN empId INT specifies that the procedure takes one input parameter of type INT.
- The procedure body contains a SELECT statement that retrieves employee details based on the provided empId.
Example in SQL Server
In SQL Server, you can create a stored procedure using a similar syntax with CREATE PROCEDURE:
CREATE PROCEDURE GetEmployeeById
@empId INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeID = @empId;
END
In this example:
- GetEmployeeById is the name of the procedure.
- @empId INT specifies the input parameter.
- The body includes a SELECT statement to fetch employee details based on the @empId parameter.
Calling the Stored Procedure
After creating the stored procedure, you can call it as follows:
MySQL
CALL GetEmployeeById(1);
SQL Server
EXEC GetEmployeeById @empId = 1;
Additional Parameter Types
You can also define output parameters or parameters of other types:
- Output Parameters: In SQL Server, you can declare an output parameter by adding OUTPUT to the parameter declaration.
- Default Values: You can assign default values to parameters so that they are optional.
Example of an Output Parameter in SQL Server
CREATE PROCEDURE GetEmployeeName
@empId INT,
@empName NVARCHAR(100) OUTPUT
AS
BEGIN
SELECT @empName = Name FROM Employees WHERE EmployeeID = @empId;
END
You can then execute this procedure and capture the output value like this:
DECLARE @name NVARCHAR(100);
EXEC GetEmployeeName @empId = 1, @empName = @name OUTPUT;
SELECT @name; -- This will display the employee name
This should give you a good understanding of how to create and use stored procedures with parameters in both MySQL and SQL Server. Adjust the syntax as needed if you're using a different DBMS.