Stored Procedure

1
Stored Procedure

2
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

3
What are Stored Procedures

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

4

• 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.

5

• An example isexec 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 asSELECT FROM
  USERLISTThis "select" statement will return all
  data in the USERLIST table.

6
Writing Your First Stored Procedure

• Syntax-
• CREATE 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.

7
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".

8

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

9

• 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

10
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

11
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.

12
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.

13
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 "_at_" symbol.
• Here are some examples
• _at_f_name ,
• _at_l_name etc.

14

• 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, lets create the header information that
  should be a part of every stored procedure.

15
Example with Variables

• Create Header information as-
• CREATE 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.

16

• The list below shows the variable and the field
  with which it is associated (In USERLIST Table)-
• _at_loginlogin
• _at_pswdpswd_at_f_namef_name_at_l_namel_name
• _at_address_1address_1
• _at_address_2address_2
• _at_citycity
• _at_statestate
• _at_zipcodezipcode _at_emailemail

17
Now our stored procedure step look like

• CREATE PROCEDURE usp_adduser _at_login _at_pswd
  _at_f_name _at_l_name _at_address_1 _at_address_2 _at_ci
  ty _at_state _at_zipcode _at_email

18
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
• _at_login varchar(20),
• _at_pswd varchar(20), _at_f_name
  varchar(25), _at_l_name varchar(35), _at_address_1
  varchar(30),
• _at_address_2 varchar(30), _at_city
  varchar(30),
• _at_state char(2), _at_zipcode char(10), _at_email
  varchar(50)

19
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', sarang_at_test.com')

20
What does the entire stored procedure look like? 
Lets pull it all together.

• CREATE PROCEDURE usp_adduser
• _at_login varchar(20), _at_pswd varchar(20), _at_f_name
  varchar(25), _at_l_name varchar(35), _at_address_1
  varchar(30), _at_address_2 varchar(30), _at_city
  varchar(30), _at_state char(2), _at_zipcode
  char(10), _at_email varchar(50)
• AS
• INSERT INTO USERLIST (login, pswd, f_name,
  l_name, address_1, address_2, city, state,
  zipcode, email)
• VALUES (_at_login, _at_pswd, _at_f_name, _at_l_name,
  _at_address_1, _at_address_2, _at_city, _at_state, _at_zipcode,
  _at_email)

21
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 ' '

22
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, lets 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', ashish_at_test.com'

23
Thanks

• Sarang Dalal