Passing Parameters to Stored Procedures

Stored procedures can accept input parameters to customize their behavior and make them more flexible and reusable. Parameters allow you to pass values into the stored procedure at runtime, enabling you to perform operations based on dynamic data.

Table of Contents

Declaring Parameters

To define parameters in a stored procedure, you need to specify their names and data types. Parameters act as placeholders that will be replaced with actual values when the procedure is executed. Here’s the syntax to declare parameters:

CREATE PROCEDURE procedure_name
    @parameter1 data_type,
    @parameter2 data_type,
    ...
AS
BEGIN
    -- SQL statements here
END;

procedure_name: Specifies the name of the stored procedure.
@parameter1, @parameter2, …: Specifies the names and data types of the input parameters.

CREATE PROCEDURE get_customer_orders
    @customer_id INT,
    @order_status VARCHAR(20)
AS
BEGIN
    SELECT order_id, order_date, total_amount
    FROM orders
    WHERE customer_id = @customer_id
    AND status = @order_status;
END;

In the example above, the stored procedure “get_customer_orders” accepts two parameters: “@customer_id” of type INT and “@order_status” of type VARCHAR(20).

Executing Stored Procedures with Parameters

When executing a stored procedure with parameters, you need to provide values for each parameter. You can do this using the EXEC or EXECUTE statement, followed by the procedure name and the parameter values.

EXEC get_customer_orders @customer_id = 123, @order_status = 'Completed';

The stored procedure “get_customer_orders” is executed with a customer ID of 123 and an order status of ‘Completed’.

Default Parameter Values

You can assign default values to parameters in a stored procedure, allowing you to omit passing a value when executing the procedure. If a value is not provided, the default value will be used instead.

CREATE PROCEDURE get_customer_orders
    @customer_id INT,
    @order_status VARCHAR(20) = 'Completed'
AS
BEGIN
    SELECT order_id, order_date, total_amount
    FROM orders
    WHERE customer_id = @customer_id
    AND status = @order_status;
END;

The “@order_status” parameter has a default value of ‘Completed’. If the parameter is omitted when executing the stored procedure, the default value will be used.

By utilizing parameters in stored procedures, you can create flexible and reusable code that can be customized based on specific input values. Parameters enhance the versatility of stored procedures and allow you to perform dynamic operations and queries in your database applications.