SQL Stored Procedures: Input Parameters Explained

What Are Input Parameters?

Input parameters are variables declared in a stored procedure that accept values when the procedure is called.

They allow the procedure to perform operations based on the input data provided.

• Declared in the procedure header with a name and data type.
• Receive values from the caller during execution.
• Used within the procedure like regular variables.

Declaring Input Parameters

Input parameters are declared in the CREATE PROCEDURE statement.

Each parameter requires a name and a data type.

• Syntax example: CREATE PROCEDURE proc_name (@param1 INT, @param2 VARCHAR(50))
• Parameters are prefixed with '@' in many SQL dialects like SQL Server.

Using Input Parameters Inside Procedures

Once declared, input parameters can be used in SQL statements within the procedure.

They can filter queries, control logic, or insert data.

• Use parameters in WHERE clauses to filter results.
• Use parameters in INSERT or UPDATE statements to specify values.
• Parameters are read-only inside the procedure.

Calling Stored Procedures with Input Parameters

When executing a stored procedure, you must provide values for all input parameters.

The syntax varies slightly between SQL dialects.

• Example call: EXEC proc_name @param1 = 10, @param2 = 'example';
• Parameters can be passed positionally or by name.

Example: Creating and Using a Stored Procedure with Input Parameters

Let's create a simple stored procedure that accepts an employee ID and returns the employee's name.