Transcript StoredProcedure

Stored Procedure
Objective
At the end of the session you will be
able to know :
 What are Stored Procedures?
 Create a Stored Procedure
 Execute a Stored Procedure
What are Stored Procedures
“A stored procedure is an already
written SQL statement that is saved
in the database.”
If you find yourself using the same query over and
over again, it would make sense to put it into a stored
procedure.
When you put this SQL statement in a stored
procedure, you can then run the stored procedure
from the database's command environment using the
exec command.
An example is:
exec usp_displayallusers
The name of the stored procedure is "usp_displayallusers", and
"exec" tells SQL Server to execute the code in the stored
procedure.
code inside the stored procedure can be something as simple
as:
SELECT * FROM USERLIST
This "select" statement will return all data in the USERLIST table.
Writing Your First Stored Procedure
SyntaxCREATE PROCEDURE [PROCEDURE NAME] AS
Procedure Name is the name of the stored procedure stored in
the database.
&
After the "AS" entry, you will simply enter SQL code as you would
in a regularly query.
Example
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".
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
your stored procedure should look like this:
CREATE PROCEDURE
usp_displayallusers
AS
SELECT * FROM USERLIST
To run your stored procedure, use exec command with SP:
exec usp_displayallusers
More Sophisticated Stored Procedures
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.
Input Variables
If you are inserting new records, you will need to get the data
from somewhere.
Updating existing records also involves simply getting the data.
In both INSERT and UPDATE statements, it is necessary to pass
data to the stored procedure.
For INSERT, UPDATE, and SELECT statements you can pass
the data to your stored procedure using variables.
Input variables are essentially "storage" for data that you want to
pass to your stored procedure.
Variable Declaration
Inside your stored procedure, you will declare variables
at the top of the stored procedure.
You can name a variable most anything you want, though it is
best to stick with meaningful works and abbreviations.
The only real requirement is that you begin your variable with the
"@" symbol.
Here are some examples:
@f_name ,
@l_name etc.
For every data element you want to pass, you will need
to declare a variable.
Declaring a variable is quite easy.
You decide on a name and a datatype (integer, text,
etc.), and indicate the name and datatype at the top of
the procedure(below the "CREATE PROCEDURE" line).
First, let’s create the header information that should be a
part of every stored procedure.
Example with Variables
Create Header information asCREATE PROCEDURE usp_adduser
/*
We will put the variables in here, later
*/
We will need to create a variable for every value we may need to
pass.
The best way to address this issue is to create a variable for
every column in USERLIST.
The list below shows the variable and the field with which it is
associated (In USERLIST Table):-
@login—login
@pswd—pswd
@f_name—f_name
@l_name—l_name
@address_1—address_1
@address_2—address_2
@city—city
@state—state
@zipcode—zipcode & @email—email
Now our stored procedure step look like
CREATE PROCEDURE usp_adduser
@login
@pswd
@f_name
@l_name
@address_1
@address_2
@city
@state
@zipcode
@email
Add Datatypes to each variables
Next, add datatypes to each of the variables.
The datatype assigned to the variable should match the datatype
assigned to the corresponding column in the database,
Separate all variables (except the last one), with a comma.
CREATE PROCEDURE usp_adduser
@login varchar(20),
@pswd varchar(20),
@f_name varchar(25),
@l_name varchar(35),
@address_1 varchar(30),
@address_2 varchar(30),
@city varchar(30),
@state char(2),
@zipcode char(10),
@email varchar(50)
ADD INSERT COMMAND TO SP
This stored procedure will add a new record to the USERLIST
table, so we should use an INSERT statement. The SQL should
be:
INSERT INTO USERLIST (login, pswd, f_name, l_name, address_1,
address_2, city, state, zipcode, email)
VALUES (‘sarang', ‘sarang123', ‘Sarang', ‘Dalal', ‘Srinagar',
‘Kalewadi', ‘Pune’,'MH', ‘411017', ‘[email protected]')
What does the entire stored procedure look like?
Let’s pull it all together.
CREATE PROCEDURE usp_adduser
@login varchar(20),
@pswd varchar(20),
@f_name varchar(25),
@l_name varchar(35),
@address_1 varchar(30),
@address_2 varchar(30),
@city varchar(30),
@state char(2),
@zipcode char(10),
@email varchar(50)
AS
INSERT INTO USERLIST (login, pswd, f_name, l_name, address_1,
address_2, city, state, zipcode, email)
VALUES (@login, @pswd, @f_name, @l_name, @address_1, @address_2,
@city, @state, @zipcode, @email)
Execute Stored Procedure
Now, we have a stored procedure that can accept external data.
What do we do with it? How do we get the data?
Using exec command
The command will be:
exec usp_adduser
There is still the issue of how to get our data into the stored
procedure.
Otherwise, all those variables will be useless.
To get data into our stored procedure, simply add the information
(in single quotes ' ') after the execute statement.
exec usp_adduser ' '
To Pass many Parameters with exec
Remember to pass as many parameters as you have variables,
otherwise SQL Server will throw an error.
Since we have ten variables, your execute statement should look
like this:
exec usp_adduser ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' '
Next, let’s include the data that we will want to pass to
usp_adduser.
Your execute statement will look like:
exec usp_adduser ‘Ashish', ‘Ashish123', ‘Ashish', ‘Bisen', ‘Bhoot
Bangla Street', ' ', ‘Pune', 'MH', '02116', ‘[email protected]'
Thanks
…Sarang Dalal