How to Add SQL Parameters
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.
急速PC28彩票Click on “SQL Server Management Studio.” The Management Studio program runs and displays the “Connect to Server” dialog.
急速PC28彩票Select a SQL server in the “Connect to Server” dialog. Set the “Authentication” pull-down list to “Windows Authentication” and click the “Connect” button.
急速PC28彩票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.
急速PC28彩票Click in the text editor and type a “Create Procedure” statement such as the following example:
急速PC28彩票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:
急速PC28彩票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
急速PC28彩票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:
SQL assigns the number "0000105020" to the parameter order_id_parameter and the stored procedure changes the order status for that order number only.
- Ablestock.com/AbleStock.com/Getty Images