How to Add SQL Parameters

by John Papiewski
Parameters let you fine tune SQL stored procedures.

Parameters let you fine tune SQL stored procedures. Images

The Microsoft SQL Server database software uses the Structured Query Language to process business transactions and maintain the database’s structure. A software developer who uses a particular SQL statement frequently can turn it into a database object called a stored procedure -- a piece of code the database stores in a fast, optimized form. Though a precompiled piece of SQL programming is helpful in some instances, SQL allows more flexibility through items called parameters. By altering the stored procedure, you can add parameters to it, letting you write more general, useful programs.


Click the “Start” button. Move the mouse cursor to “All Programs,” then to “Microsoft SQL Server.” Windows shows you a list of SQL Server programs.


Click on “SQL Server Management Studio.” The Management Studio program runs and displays the “Connect to Server” dialog.


Select a SQL server in the “Connect to Server” dialog. Set the “Authentication” pull-down list to “Windows Authentication” and click the “Connect” button.


Click “New Query” in the Management Studio’s main toolbar. The Management Studio displays a large text editor area on the screen’s right side.


Click in the text editor and type a “Create Procedure” statement such as the following example:

CREATE PROCEDURE set_shipped_orders AS UPDATE orders SET order_status = ‘SHIPPED’ WHERE order_status = ‘PICKED’

After typing the text, execute it by clicking the “Run” symbol, a right-pointing green triangle in the Management Studio toolbar. You can now invoke the stored procedure by typing its name, “set_shipped_orders,” then clicking the Run symbol. Note that the Update statement above changes the order status to ‘SHIPPED’ for every order having the status ‘PICKED.’ Though useful, you might want to make it more selective. Using a parameter, you can change the stored procedure to update shipping status for one order at a time.


Type an “Alter Procedure” statement to add parameters as the following SQL code illustrates:

ALTER PROCEDURE set_shipped_orders @order_id_parameter CHAR(10) AS UPDATE orders SET order_status = ‘SHIPPED’ WHERE order_status = ‘PICKED’ AND order_id = @order_id_parameter

Click the Run symbol to execute this text. Note the parameter, ‘order_id_parameter’ on line two above. After you execute the Alter Procedure statement, you can then use the set_shipped orders stored procedure with a parameter, such as this example:

set_shipped_orders 0000105020

SQL assigns the number "0000105020" to the parameter order_id_parameter and the stored procedure changes the order status for that order number only.

About the Author


Photo Credits

  • Images
bibliography-icon icon for annotation tool Cite this Article