Sql Server Stored Procedure

what is stored procedure?

-- written by Tom O'Neill


A stored procedure is an already written SQL statement that is saved in the database.

to execute a stored procedure :

- exec usp_name

create stored procedure:
3 things required:
a dbms
a dbase inside a dbms
query analyzer
- write the stored procedure ans copy into the new stored procedure window in sql server.

Writing Your First Stored Procedure
-- written by Tom O'Neill

Finally!!! It is time to write your first stored procedure (assuming you have created your database). In SQL Server, under your database tree, select the "Stored Procedures" option from Enterprise Manager (when you gain more experience, you can use Query Analyzer to create stored procedures). There will be a number of system generated stored procedures there already. Just ignore them. Your next step is to right click on any of the existing stored procedures (don’t worry, you won’t actually use them), then select "New Stored Procedure . . ." This will open the stored properties window I discussed above. The following code will appear already in the window:

CREATE PROCEDURE [PROCEDURE NAME] AS

The first thing I usually do is provide some spacing (we’ll need it later). This isn’t required, and as you write more stored procedures, you will find a style with which you are comfortable.


/*
We will use this area for comments
*/

CREATE PROCEDURE [PROCEDURE NAME]

/*
We will put the variables in here, later
*/

AS

/*
This is where the actual SQL statements will go
*/



So far, it is pretty simple. Let’s look at the top comments section first,


/*
We will use this area for comments
*/


When you write stored procedures (especially for a business or academic project), you never know who will eventually have to alter the code. This top section is useful for comments about the stored procedure, a change log, and other pertinent information. While this is not required, it is just a good programming habit. For this exercise, make it look like this:


/*
Name: usp_displayallusers
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
*/


Of course, you can use your own name and today’s date.

The next section will change only slightly. Every stored procedure needs the words "CREATE PROCEDURE" followed by the name you want to assign to the stored procedure. While not required, stored procedure names usually begin with the prefix "usp_".


CREATE PROCEDURE usp_displayallusers


This tells the database that you are creating a stored procedure named "usp_displayallusers". So far, your stored procedure should look like this:


/*
Name: usp_displayallusers
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


The next step is to think about variables. Since this is our first stored procedure together, we won’t deal with them yet. Just keep in mind that they are usually added after the "CREATE PROCEDURE" line. Since we don’t have variables, the next step is quite simple. Put the word "AS" beneath the create procedure line.


CREATE PROCEDURE usp_displayallusers
AS

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:


/*
Name: usp_displayallusers
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

AS

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:



exec usp_displayallusers


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.