How do I pass a stored procedure parameter in SQL?

Question

In previous lesson we discussed creating stored procedures. Now lets get into next steps for stored procedures. In this article we will discuss adding parameters to a stored procedures.

Nội dung chính Show

Creating a stored procedure with one parameter
Executing a stored procedure with one parameter
Creating a stored procedure with multiple parameters
Using named parameters
Creating text parameters
Creating optional parameters
Using NULL as the default value
How to pass stored procedure parameter in SQL?
How to add parameter in stored procedure in SQL Server?
How to pass default parameter in stored procedure in SQL Server?
Can pass 3 types of parameters to Stored Procedures What are they?

Stored Procedures Parameters

Parameters are variable values that we want to pass to our stored procedures to alter their outputs wrt to the input variable.

We will discuss adding parameters, executing stored procedures with parameters, handling multiple parameters and optional parameters.

Adding Parameters

USE database_name
GO -- Create a new batchCREATE PROC SP_LIST_COLUMN_1(@Parameter1 AS INT)
AS
BEGIN
    SELECT COLUMN_1
    FROM [DBO].TABLE_NAME
    WHERE COLUMN_1 < Parameter1
END

To add the parameters we simply add parentheses after the name of our stored procedure in CREATE PROC clause. Parameter name starts with a @ sign followed by parameter name. Then data type for parameter is defined using AS key word and keyword for corresponding data type (

EXECUTE SP_LIST_COLUMN_1 100

0 in above example).

2. Execute Procedure with Parameters

EXECUTE SP_LIST_COLUMN_1 100

To execute procedure with parameters simply write the parameter value in

EXECUTE SP_LIST_COLUMN_1 100

1 clause followed by procedure name.

3. Adding Multiple Parameters

USE database_name
GO -- Create a new batchCREATE PROC SP_LIST_COLUMN_1
    (
        @Parameter1 AS INT,
        @Parameter2 AS INT,
        @Parameter3 AS VARCHAR(MAX)
    )
AS
BEGIN
    SELECT COLUMN_1, COLUMN_2
    FROM [DBO].TABLE_NAME
    WHERE 
        (COLUMN_1 < Parameter1 AND COLUMN_1 >= Parameter2) AND
        COLUMN_2 LIKE '%' + @Parameter3 + '%'
END

To add more than one parameter we simply add subsequent parameters followed by the first. See the above example. To execute the stored procedure simply add parameter values after the first value as below.

EXEC SP_LIST_COLUMN_1 100, 50, 'Toronto'

For more clarity parameters should be named so that developer is not lost while referring back to old scripts. Naming can be done as follows.

EXEC SP_LIST_COLUMN_1 @Parameter1=100, @Parameter2=50, @Parameter3='Toronto'

4. Optional Parameters

USE database_name
GO -- Create a new batchCREATE PROC SP_LIST_COLUMN_1
    (
        @Parameter1 AS INT,
        @Parameter2 AS INT = 0,
        @Parameter3 AS VARCHAR(MAX)
    )
AS
BEGIN
    SELECT COLUMN_1, COLUMN_2
    FROM [DBO].TABLE_NAME
    WHERE 
        (COLUMN_1 < Parameter1 AND COLUMN_1 >= Parameter2) AND
        COLUMN_2 LIKE '%' + @Parameter3 + '%'
END

To add an optional parameter you simply define a default value for that parameter. In above example we set

EXECUTE SP_LIST_COLUMN_1 100

2 as 0. Now let us execute the above procedure with its default (optional) parameter.

EXEC SP_LIST_COLUMN_1 @Parameter1=100, @Parameter3='Toronto'

So that’s all for this lessons and parameters. Next up we will discuss the variables and I highly recommend reading output parameters story to have complete understanding of variables and parameters. Check the previous lesson on creating stored procedures if you haven’t don so.

In the previous tutorial, you have learned how to create a simple stored procedure that wraps a

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

7 statement. When you call this stored procedure, it just simply runs the query and returns a result set.

In this tutorial, we will extend the stored procedure which allows you to pass one or more values to it. The result of the stored procedure will change based on the values of the parameters.

Creating a stored procedure with one parameter

The following query returns a product list from the

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

8 table in the sample database:

SELECT
    product_name,
    list_price
FROM 
    production.products
ORDER BY
    list_price;
Code language: SQL (Structured Query Language) (sql)

You can create a stored procedure that wraps this query using the

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

9 statement:

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

However, this time we can add a parameter to the stored procedure to find the products whose list prices are greater than an input price:

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

In this example:

First, we added a parameter named

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

0 to the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

1 stored procedure. Every parameter must start with the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

2 sign. The

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

3 keywords specify the data type of the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

0 parameter. The parameter must be surrounded by the opening and closing brackets.

Second, we used

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

0 parameter in the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

6 clause of the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

7 statement to filter only the products whose list prices are greater than or equal to the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

0.

Executing a stored procedure with one parameter

To execute the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

1 stored procedure, you pass an argument to it as follows:

EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)

The stored procedure returns all products whose list prices are greater than or equal to 100.

If you change the argument to 200, you will get a different result set:

EXEC uspFindProducts 200;
Code language: SQL (Structured Query Language) (sql)

Creating a stored procedure with multiple parameters

Stored procedures can take one or more parameters. The parameters are separated by commas.

The following statement modifies the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

1 stored procedure by adding one more parameter named

EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)

1 to it:

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

Once the stored procedure is modified successfully, you can execute it by passing two arguments, one for

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

0 and the other for

EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)

1:

EXECUTE uspFindProducts 900, 1000;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

Using named parameters

In case stored procedures have multiple parameters, it is better and more clear to execute the stored procedures using named parameters.

For example, the following statement executes the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

1 stored procedure using the named parameters

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

0and

EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)

1:

EXECUTE uspFindProducts 
    @min_list_price = 900, 
    @max_list_price = 1000;
Code language: SQL (Structured Query Language) (sql)

The result of the stored procedure is the same however the statement is more obvious.

Creating text parameters

The following statement adds the

EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)

7 parameter as a character string parameter to the stored procedure.

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

In the

EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)

8 clause of the

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

7 statement, we added the following condition:

product_name LIKE '%' + @name + '%'
Code language: SQL (Structured Query Language) (sql)

By doing this, the stored procedure returns the products whose list prices are in the range of min and max list prices and the product names also contain a piece of text that you pass in.

Once the stored procedure is altered successfully, you can execute it as follows:

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

0

In this statement, we used the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

1 stored procedure to find the product whose list prices are in the range of 900 and 1,000 and their names contain the word

EXEC uspFindProducts 200;
Code language: SQL (Structured Query Language) (sql)

1.

The following picture shows the output:

Creating optional parameters

When you execute the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

1 stored procedure, you must pass all three arguments corresponding to the three parameters.

SQL Server allows you to specify default values for parameters so that when you call stored procedures, you can skip the parameters with default values.

See the following stored procedure:

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

1

In this stored procedure, we assigned

EXEC uspFindProducts 200;
Code language: SQL (Structured Query Language) (sql)

3 as the default value for the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

0 parameter and

EXEC uspFindProducts 200;
Code language: SQL (Structured Query Language) (sql)

5 as the default value for the

EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)

1 parameter.

Once the stored procedure is compiled, you can execute it without passing the arguments to

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

0 and

EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)

1 parameters:

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

2

In this case, the stored procedure used

EXEC uspFindProducts 200;
Code language: SQL (Structured Query Language) (sql)

3 for

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

0 parameter and

EXEC uspFindProducts 200;
Code language: SQL (Structured Query Language) (sql)

5 for the

EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)

1 parameter when it executed the query.

The

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

0 and

EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)

1 parameters are called optional parameters.

Of course, you can also pass the arguments to the optional parameters. For example, the following statement returns all products whose list prices are greater or equal to

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

5 and the names contain the word

EXEC uspFindProducts 200;
Code language: SQL (Structured Query Language) (sql)

1:

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

3

Using NULL as the default value

In the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

1 stored procedure, we used

EXEC uspFindProducts 200;
Code language: SQL (Structured Query Language) (sql)

5 as the default maximum list price. This is not robust because in the future you may have products with the list prices that are greater than that.

A typical technique to avoid this is to use

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

9 as the default value for the parameters:

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

4

In the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

6 clause, we changed the condition to handle

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

9 value for the

EXEC uspFindProducts 100;
Code language: SQL (Structured Query Language) (sql)

1 parameter:

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

5

The following statement executes the

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

1 stored procedure to find the product whose list prices are greater or equal to 500 and names contain the word

EXECUTE uspFindProducts 900, 1000;
Code language: SQL (Structured Query Language) (sql)

4.

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;
Code language: SQL (Structured Query Language) (sql)

6

In this tutorial, you have learned how to create and execute stored procedures with one or more parameters. You also learned how to create optional parameters and use NULL as the default values for the parameters.

How to pass stored procedure parameter in SQL?

There are two ways to pass parameters to a stored procedure using SQLExec. One way, which works across all versions of Visual FoxPro, is to build the SQL command as a string variable. The advantage of this method is that you can check the string and see exactly which SQL command you are passing to the back end.

How to add parameter in stored procedure in SQL Server?

To add the parameters we simply add parentheses after the name of our stored procedure in CREATE PROC clause. Parameter name starts with a @ sign followed by parameter name. Then data type for parameter is defined using AS key word and keyword for corresponding data type ( INT in above example).

How to pass default parameter in stored procedure in SQL Server?

If you add a parameter when creating a stored procedure, you can provide a default value so that the execution statement is not required to pass input value to this parameter. To provide a default value to a parameter, you should use this format: "@parameter_name data_type = default_value".