1 / 27

Stored Procedures

Stored Procedures. Dr. Ralph D. Westfall May, 2011. Getting Database Data. when using a database, there are two places where a detailed request for data can be located inside a program that is separate from the database inside the database itself

wfong
Download Presentation

Stored Procedures

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 Procedures Dr. Ralph D. Westfall May, 2011

  2. Getting Database Data • when using a database, there are two places where a detailed request for data can be located • inside a program that is separate from the database • inside the database itself • a stored procedure is a previously created query/command in a database

  3. Stored Procedures • code to manipulate database • work with data (data manipulation) • manage tables (data definition) • precompiled to run faster • better security • can give users access to data from stored procedures rather than to whole tables

  4. Similarities with Subroutines • can be reused to avoid recoding, make code simpler, avoid redundancy, reduce resource requirements • easier to update than multiple copies • can be used in more than one program • easier to migrate applications to other platforms or scale up to larger volumes

  5. Stored Procedures with SQL Server Management Studio • Click New Query button to create • SSMS doesn’t always seem to work if don’t qualify table and field names • Putting square brackets around field and table names may also help • Right-click a table name>Select Top 1000 Rows to see syntax punctuation • Or refresh Intellisense: [Ctrl]+[Shift]+[R]

  6. Start a New Query • open SQL Server Management Studio and attach the table in OlyNations_Data.zip (OlyNations) • select it and click New Query in toolbar • type SELECT and drag/drop Columns icon from dbo.nations2 and dbo.currencies Tables after SELECT (add comma between groups, and then delete duplicate CID)

  7. Database Names • in a new line, type the word FROM • drag icons for tables (dbo.nations2 and dbo.currencies) and drop them after the from in the query (separate them with a comma) • copy these database table names and paste them before any fields that have the same names in both tables ("Ambiguous column names"), using a dot as a connector • start a new line after table names (Enter)

  8. Where Clause • type the word WHERE in the new line • paste table names from previous line after FROM • replace the comma with = • put a dot (.) after each table name and paste field name (CID) after each table name • click the Execute button in Toolbar • fix errors if necessary • save query with appropriate name for future use

  9. Stored Procedure from Query • Under name of database, expand Programmability • right-click Stored Procedures • right-click Stored Procedures>New Stored Procedure • in CREATE PROCEDURE line, change <Procedure Name etc. > to what you want to call it

  10. Parameters • add parameters with the form of: • @[name] as [datatype] = null [or default], [next parameter] e.g., @country as char(25) = null [or 'Chile'], @golds as int = null, [comma separated] @pop as decimal(7,2) = null [or 3.2] • get datatypes from Columns definitions • default values can help with testing

  11. Add Query • replace SELECT < etc. > with the query you created earlier • add a parameter based selection after the where clause (or make it be the where clause) e.g., and Country = @country

  12. Compile • click Execute button • fix errors if necessary • close query builder window and save file with a filename that identifies it as creating this stored procedure • refresh Object Explorer and verify that stored procedure was created • Rename it to start with usp_

  13. Testing a Stored Procedure • Put database name, stored procedure name, parameter argument(s) into a new query as follows: USE OlyNations; GO EXECUTE dbo.uspGetAll @pop = 100.0 ; GO

  14. Modifying Stored Procedures • right click the stored procedure name in SQL Server Management Studio>Modify • change code as necessary and then click Execute button to store it in the database • close the code window and save the code with a filename that identifies it as modifying this stored procedure

  15. Using Stored Procedures in VB • create or use a VB project that works with a database • in a multi-tier application, most of the changes will be in the data tier • but business rules tier will have to be changed to provide parameters rather than SQL strings

  16. Coding the VB Project • add a ComboBox to select countries, a ListBox for outputs and a Button to trigger selections • download and unzip OlySQLppt.zip • Project>Add Existing Item>select DT2.vb from subdirectory of file you just unzipped

  17. Modify Data Tier Code • AcquireData Function receives a parameter value rather than SQL string • declarations • SQLCommand is stored procedure • SqlParameter is one of the parameters (@[name] in the stored procedure Dim sqlCmd As SqlCommand Dim sqlParm As SqlParameter

  18. More Added Data Tier Code • add the following after the statement that opens the connection [con.Open()] sqlCmd = New SqlCommand() sqlCmd.Connection = con sqlCmd.CommandType = _ CommandType.StoredProcedure sqlCmd.CommandText = "GetNations"

  19. Even More Data Tier Code • rest of added block sqlParmCountry = New _ SqlParameter("@country", selection) sqlParmCountry.Direction = _ ParameterDirection.Input sqlParmCountry.DbType = DbType.String sqlCmd.Parameters.Add(sqlParmCountry)

  20. Test Code • run it and debug as necessary

  21. Multiple Parameter Selection • modify stored procedure to make all parameters default to null • make query ignore missing parameters • change 1st parameter selection: and (@country is null OR Country = @country) • additional parameters in same pattern 'notes

  22. User Interface Modifications • declare each selection and assign selected value or default If ComboBox1.SelectedIndex >= 0 Then contry = ComboBox1.SelectedItem.ToString() Else contry = "none" End If

  23. UI Modifications - 2 • provide error message if no selections are made If [ ]= "none" And [ ] = "none" Then MsgBox("Select [ ] or [ ], or both") Else … [previous line of code to call data tier, modified to handle more arguments

  24. Data Tier Modifications • modify Function to handle additional argument(s) • wrap 1st parameter block in If structure If contryName <> "none" Then • add an If structure for the next parameter • see complete project code

  25. DataGridView UsingStored Procedure • 'notes

  26. Stored Procedure • Pretty lengthy example using stored procedures. How Do I: Understand Data? • Visual Basic programmers will explore how connecting data in a database to controls in Windows Forms applications really works by looking deeper into the DataSet, BindingSource and TableAdapter objects and walking through examples of using data in code.

  27. Or Use Designer for Query • right-click database name>New Stored Procedure then click Query>Design Query in Editor • select tables you want to use>Add>Close • select columns you want>OK

More Related