SQL Parameters In Stored Procedures

Learn to use parameters in SQL stored procedures, and make your code more versatile and reliable.

In this tutorial, we will dive into stored procedures. Learning about passing one or more parameters to it. This will cause the result of the stored procedure to change based on the values of the parameters inputted.

Stored Procedures with one parameter

We will create an example to show how the parameters work. Let’s say that we have a product table with lots of information. We can add a parameter to the stored procedure to return the products whose prices are lower than the input price:

CREATE PROCEDURE usp_find_products
  (@max_price AS DECIMAL)
AS
BEGIN
    SELECT
        name, price
    FROM 
        products
    WHERE
        price <= @max_price
END;

Let’s analyze this example:

• We added a parameter named @max_price to the procedure usp_find_products. Every parameter must start with the @ sign. To specify the data type of our parameter we used AS DECIMALbut we can also use directlyDECIMAL. The passed parameter must be surrounded by both the opening and closing parenthesis.
• Then we used @max_price in the WHERE clause to filter only the products whose prices are lesser or equal to @max_price.

To execute the usp_find_products stored procedure, in this case, you should pass an argument:

EXEC usp_find_products 50;

If you change the argument to 100, a different result will be returned:

EXEC usp_find_products 100;

Stored procedures with multiple parameters

When writing a stored procedure with several parameters, these are separated by commas.

Let’s modify our previous stored procedure uspFindProducts to determine not only a max price but also a min price.

ALTER PROCEDURE usp_find_products(
    @min_price AS DECIMAL
    ,@max_price AS DECIMAL
)
AS
BEGIN
    SELECT
        name, price
    FROM 
        products
    WHERE
        price >= @min_price AND
        price <= @max_price
END;EXECUTE uspFindProducts 900, 1000;

Then we execute the stored procedure and pass it 2 values for the parameters: EXECUTE usp_find_products 100, 200; Which returns for example the following output:

Usually, it’s better to execute the stored procedures using named parameters. The following code will execute the same as the previous calling:

EXECUTE usp_find_products @min_list_price = 900, @max_list_price = 1000;

Defining optional parameters

In all the previous examples, it is required to pass values for all parameters defined. But SQL allows us to define a default value for a parameter, which will let the parameter take that value in case of not specifically passing it on the EXECUTE.

See the following stored procedure as an example:

ALTER PROCEDURE usp_find_products(
    @min_price AS DECIMAL = 0
    ,@max_price AS DECIMAL = 1000
)
AS
BEGIN
    SELECT
        name, price
    FROM 
        products
    WHERE
        price >= @min_price AND
        price <= @max_price
END;

We have altered the stored procedure, and assigned 0 as the default value for the @min_price parameter and 1000 as the default value for the @max_price.

Then, you will be able to execute without passing the values for@min_price and @max_price: EXECUTE usp_find_products. It is taking 0 and 1000 as default values and will return the products that meet those conditions.

Note that you can still pass the values to the optional parameters when calling the execute. In this case, you can pass values for none, either, or both parameters. As all the following examples show:

EXECUTE usp_find_products; 
EXECUTE usp_find_products @min_price=10; 
EXECUTE usp_find_products @min_price=10, @max_price=100;

Know that you can also set a parameter default value to NULL. Note that this is typically a technique to avoid.

Output parameters

Stored procedures can also have output parameters. For example, let’s alter our previous procedure to also return the number of products through the output parameter @count:

ALTER PROCEDURE usp_find_products(
    @min_price AS DECIMAL = 0
    ,@max_price AS DECIMAL = 1000
    ,@count INT OUTPUT
)
AS
BEGIN
    SELECT
        name, price
    FROM 
        products
    WHERE
        price >= @min_price AND
        price <= @max_price
    SELECT @count = @@ROWCOUNT;
END;

Above we have created an output parameter called@count to store the number of found products: @count INT OUTPUT. We assigned the number of rows returned by the select query (@@ROWCOUNT) to the @count parameter.

@@ROWCOUNT is a system variable that takes the value of the number of rows returned by the preceding select statement.

The use of output parameters serves as a way to retrieve data calculated inside of a stored procedure and put it into a parameter to use outside of the procedure.

In this tutorial, you have learned how to work with stored procedures having one or more parameters. You also learned how to create optional parameters and use the output parameter.

Thanks for reading, and have a great rest of your day! Keep coding.