1 / 23

Stored Procedure

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

dante
Download Presentation

Stored Procedure

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 is:exec usp_displayallusersThe 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 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 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_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 "@" symbol. Here are some examples: @f_name , @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, let’s 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):- @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

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

  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 @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)

  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@test.com')

  20. 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)

  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, 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', ‘ashish@test.com'

  23. Thanks …Sarang Dalal

More Related