The Microsoft SQL Server database software lets you create stored procedures, which are functions saved on the server. You can call these functions several times in your applications, so you do not need to type out the code each time you run a specific set of instructions. You use the "create" SQL keyword to create a stored procedure, and you use the "alter" keyword to replace the code in the procedures.
Click the Windows "Start" button and select "All Programs." Click "Microsoft SQL Server" in the list of program groups, then click "SQL Server Management Studio." This shortcut opens the main configuration and coding screen for your database.
Click the "New Query" button at the top of the Management Studio window. This button opens a SQL editor where you can create and alter your stored procedures.
Create a stored procedure. The following code is an example of how you create a stored procedure. The "create" keyword is required. After the keyword, the name of the stored procedure is entered. All the code that follows are the statements executed after you call a stored procedure by its name.
create proc myprocedure
select * from customers
This stored procedure named "myprocedure" selects all records from the "customers" database. Press "F5" to execute the statement. The stored procedure is created on the database.
Alter the stored procedure. To alter a stored procedure, you replace the "create" keyword with "alter." You then change the code to reflect your changes. Using the stored procedure from Step 3, the following code changes the stored procedure to retrieve records from the "orders" table:
alter proc myprocedure
select * from orders
Press the "F5" key to change the stored procedure.
Type "exec myprocedure" to test the code. The results display a list of records from the orders table.
Stored Procedures for an SQL Server Express
Microsoft SQL Server Express is a free version of Microsoft's SQL Server, which is a resource for administering and creating databases, and...
How to Create a SQL Query in Microsoft Access
SQL queries use Structured Query Language (SQL), a standard scripting language, to make requests from databases. You can use four types of...
How to Repair MS SQL 2005
SQL Server 2005 is a program designed by Microsoft, and it is intended for Windows users. When trying to apply a software...
How to Connect to MS SQL Using VBScript
Visual Basic Scripting, or VBS, lets you connect to your internal database to retrieve data for your users. VBS is typically used...