How to Create or Replace Procedures in MS SQL

Save

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

    as

    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

    as

    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.

References

Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!