Stored Procedures For Beginners
We are telling the database that we want to create a stored procedure that is called "usp_displayallusers" that is characterized by the code that follows. After the "AS" entry, you will simply enter SQL code as you would in a regularly query. For our first, we will use a SELECT statement:
SELECT * FROM USERLIST
Now, your stored procedure should look like this:
Description: displays all records and columns in USERLIST table
Author: Tom O’Neill
Modification Log: Change
Description Date Changed By
Created procedure 7/15/2003 Tom O’Neill
CREATE PROCEDURE usp_displayallusers
SELECT * FROM USERLIST
Congratulations, you have written your first stored procedure. If you authored the procedure in a text editor, now would be a good time to copy it into the New Stored Procedure window in SQL Server. Once you have done so, click the "Check Syntax" box. This is a great troubleshooting tool for beginners and experts alike. When SQL Server tells you "Syntax check successful!", you can click OK to save your stored procedure. To view the procedure, simply double-click usp_displayallusers in the Stored Procedures window. To run your stored procedure, open the Query Analyzer and type:
Then, click the green "play" button to run the query. You will see that the procedure has run successfully.
It can be frustrating to start from scratch. Right now, you can think of all the things you want to accomplish with stored procedures; you just need to learn how! That will happen next. Let’s take a look at some more useful stored procedures.
More Sophisticated Stored Procedures
In this section, we are going to address a few new topics. In addition to writing SELECT queries, you are going to want to insert, update, and delete database records. Also, you will probably want to pass information from outside the query. Since inserts and updates require some sort of data input to be useful, our first topic will be variables. From there, we will use data stored in variables for inserts and updates.
Note: In this article, we will only address input variables (variables that pass data to the SQL statement in the stored procedure). There are various types of outputs and returns, and they can become quite complex. Since this article is an introduction, we will leave outputs for another time.