SELECT product_name, list_priceFROM production.productsORDER BY list_price;<\/td><\/tr><\/tbody><\/table>\n\n\n\n
You can create a stored procedure that wraps this query using the CREATE PROCEDURE<\/code> statement:<\/p>\n\n\n\n
1234567891011<\/td>
CREATE PROCEDURE uspFindProductsASBEGIN SELECT product_name, list_price FROM production.products ORDER BY list_price;END;<\/td><\/tr><\/tbody><\/table>\n\n\n\n
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:<\/p>\n\n\n\n
12345678910111213<\/td>
ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)ASBEGIN SELECT product_name, list_price FROM production.products WHERE list_price >= @min_list_price ORDER BY list_price;END;<\/td><\/tr><\/tbody><\/table>\n\n\n\n
In this example:<\/p>\n\n\n\n
First, we added a parameter named @min_list_price<\/code> to the uspFindProducts<\/code> stored procedure. Every parameter must start with the @<\/code> sign. The AS DECIMAL<\/code> keywords specify the data type of the @min_list_price<\/code> parameter. The parameter must be surrounded by the opening and closing brackets.<\/li>
Second, we used @min_list_price<\/code> parameter in the WHERE<\/code> clause of the SELECT<\/code> statement to filter only the products whose list prices are greater than or equal to the @min_list_price<\/code>.<\/li><\/ul>\n\n\n\n
Executing a stored procedure with one parameter<\/h3>\n\n\n\n
To execute the uspFindProducts<\/code> stored procedure, you pass an argument to it as follows:<\/p>\n\n\n\n
Creating a stored procedure with multiple parameters<\/h2>\n\n\n\n
Stored procedures can take one or more parameters. The parameters are separated by commas.<\/p>\n\n\n\n
The following statement modifies the uspFindProducts<\/code> stored procedure by adding one more parameter named @max_list_price<\/code> to it:<\/p>\n\n\n\n
1234567891011121314151617<\/td>
ALTER PROCEDURE uspFindProducts( @min_list_price AS DECIMAL ,@max_list_price AS DECIMAL)ASBEGIN 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;<\/td><\/tr><\/tbody><\/table>\n\n\n\n
Once the stored procedure is modified successfully, you can execute it by passing two arguments, one for @min_list_price<\/code> and the other for @max_list_price<\/code>:<\/p>\n\n\n\n
In case stored procedures have multiple parameters, it is better and more clear to execute the stored procedures using named parameters.<\/p>\n\n\n\n
For example, the following statement executes the uspFindProducts<\/code> stored procedure using the named parameters @min_list_price<\/code>and @max_list_price<\/code>:<\/p>\n\n\n\n
The result of the stored procedure is the same however the statement is more obvious.<\/p>\n\n\n\n
Creating text parameters<\/h2>\n\n\n\n
The following statement adds the @name<\/code> parameter as a character string parameter to the stored procedure.<\/p>\n\n\n\n
12345678910111213141516171819<\/td>
ALTER PROCEDURE uspFindProducts( @min_list_price AS DECIMAL ,@max_list_price AS DECIMAL ,@name AS VARCHAR(max))ASBEGIN 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;<\/td><\/tr><\/tbody><\/table>\n\n\n\n
product_name LIKE ‘%’ + @name + ‘%’<\/td><\/tr><\/tbody><\/table>\n\n\n\n
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.<\/p>\n\n\n\n
Once the stored procedure is altered successfully, you can execute it as follows:<\/p>\n\n\n\n
In this statement, we used the uspFindProducts<\/code> stored procedure to find the product whose list prices are in the range of 900 and 1,000 and their names contain the word Trek<\/code>.<\/p>\n\n\n\n
The following picture shows the output:<\/p>\n\n\n\n
<\/figure>\n\n\n\n
Creating optional parameters<\/h2>\n\n\n\n
When you execute the uspFindProducts<\/code> stored procedure, you must pass all three arguments corresponding to the three parameters.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
See the following stored procedure:<\/p>\n\n\n\n
12345678910111213141516171819<\/td>
ALTER PROCEDURE uspFindProducts( @min_list_price AS DECIMAL = 0 ,@max_list_price AS DECIMAL = 999999 ,@name AS VARCHAR(max))ASBEGIN 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;<\/td><\/tr><\/tbody><\/table>\n\n\n\n
In this stored procedure, we assigned 0<\/code> as the default value for the @min_list_price<\/code> parameter and 999,999<\/code> as the default value for the @max_list_price<\/code> parameter.<\/p>\n\n\n\n
Once the stored procedure is compiled, you can execute it without passing the arguments to @min_list_price<\/code> and @max_list_price<\/code> parameters:<\/p>\n\n\n\n
In this case, the stored procedure used 0<\/code> for @min_list_price<\/code> parameter and 999,999<\/code> for the @max_list_price<\/code> parameter when it executed the query.<\/p>\n\n\n\n
The @min_list_price<\/code> and @max_list_price<\/code> parameters are called optional parameters.<\/p>\n\n\n\n
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 6,000<\/code> and the names contain the word Trek<\/code>:<\/p>\n\n\n\n
In the uspFindProducts<\/code> stored procedure, we used 999,999<\/code> 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.<\/p>\n\n\n\n
A typical technique to avoid this is to use NULL<\/code> as the default value for the parameters:<\/p>\n\n\n\n
12345678910111213141516171819<\/td>
ALTER PROCEDURE uspFindProducts( @min_list_price AS DECIMAL = 0 ,@max_list_price AS DECIMAL = NULL ,@name AS VARCHAR(max))ASBEGIN SELECT product_name, list_price FROM production.products WHERE list_price >= @min_list_price AND (@max_list_price IS NULL OR list_price <= @max_list_price) AND product_name LIKE ‘%’ + @name + ‘%’ ORDER BY list_price;END;<\/td><\/tr><\/tbody><\/table>\n\n\n\n
In the WHERE<\/code> clause, we changed the condition to handle NULL<\/code> value for the @max_list_price<\/code> parameter:<\/p>\n\n\n\n
1<\/td>
(@max_list_price IS NULL OR list_price <= @max_list_price) <\/td><\/tr><\/tbody><\/table>\n\n\n\n
The following statement executes the uspFindProducts<\/code> stored procedure to find the product whose list prices are greater or equal to 500 and names contain the word Haro<\/code>.<\/p>\n\n\n\n
Creating a stored procedure with one parameter The following query returns a product list from the products table in the sample database: 1234567 SELECT product_name, list_priceFROM production.productsORDER BY list_price; You can create a stored procedure that wraps this query using the CREATE… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2,18,19],"tags":[],"_links":{"self":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/140"}],"collection":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/comments?post=140"}],"version-history":[{"count":1,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/140\/revisions"}],"predecessor-version":[{"id":141,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/140\/revisions\/141"}],"wp:attachment":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/media?parent=140"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/categories?post=140"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/tags?post=140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"SQL](\"http:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Stored-Procedure-Parameters-One-parameter-example.png\")
<\/figure>\n\n\n\n![\"SQL](\"http:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Stored-Procedure-Parameters-one-parameter-change-argument-example.png\")
<\/figure>\n\n\n\n![\"SQL](\"http:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Stored-Procedure-Parameters-multiple-parameters-example.png\")
<\/figure>\n\n\n\n![\"SQL](\"http:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Stored-Procedure-Parameters-text-parameter-example.png\")
<\/figure>\n\n\n\n![\"SQL](\"http:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Stored-Procedure-Parameters-Optional-Parameters.png\")
<\/figure>\n\n\n\n![\"SQL](\"http:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Stored-Procedure-Parameters-Pass-Optional-Parameters.png\")
<\/figure>\n\n\n\n![\"SQL](\"http:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Stored-Procedure-Parameters-NULL-as-default-values.png\")
<\/figure>\n","protected":false},"excerpt":{"rendered":"