stored procedures
Download
Skip this Video
Download Presentation
Stored Procedures

Loading in 2 Seconds...

play fullscreen
1 / 26

Stored Procedures - PowerPoint PPT Presentation


  • 245 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Stored Procedures' - MartaAdara


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
stored procedures

Stored Procedures

Dr. Ralph D. Westfall

May, 2009

getting database data
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 or program in a database
sql server stored procedures
SQL Server Stored Procedures
  • precompiled in SQL Server so they run faster
  • can be reused to avoid recoding
  • make code simpler
    • like subprocedures do
  • better security
    • can give users access to data from stored procedures rather than to whole tables
stored procedures4
Stored Procedures
  • code to manipulate database (retrieve data, add, change, delete) stored in database
    • like subroutine, can use in multiple programs (but not with Access database)
    • less storage: once instead of multiple copies
    • easier to update than multiple copies
    • stored queries run faster inside database
    • makes it easier to migrate applications to other platforms or scale up to larger volumes
stored procedures with vb
Stored Procedures with VB
  • create or open a Visual Basic project
  • Data>Add new Data Source to get an existing SQL Server database and select the tables that you will use
  • View>Server Explorer
    • expand the database you want, right-click Stored Procedures>Add New Stored Procedure
configure stored procedure
Configure Stored Procedure
  • change name in top line
  • http://msdn.microsoft.com/en-us/library/aa258259(SQL.80).aspx
  • http://databases.about.com/od/sqlserver/a/storedprocedure.htm
create stored procedure
Create Stored Procedure
  • right-click Stored Procedures>New Stored Procedure
stored procedures with vb11
Stored Procedures with VB
  • start SQL Server Management Studio Express
    • attach or create a database file
    • right-click database name>New Query
    • paste in a sample query (e.g., from link above) and modify it to match fields in this database)
    • run it to see results
creating stored procedures
Creating Stored Procedures
  • start SQL Server Management Studio
  • expand a database>expand Programmability>right-click Stored Procedures>New Stored Procedure
  • click Query in top menu>Specify Values for Template Parameters
  • replace values for ProcedureName, @Param[], Datatypes and Default Values
complete stored procedure
Complete Stored Procedure
  • replace SELECT statement with SQL code for the procedure
    • parameters usually are in WHERE clause WHERE [field] [compare] @[name] e.g., WHERE AGE= @age
  • use Query>Parse to verify syntax
  • click !Execute button to compile it
  • File>Save (rename it)>Save
verify stored procedure
Verify Stored Procedure
  • click Refresh icon in Object Explorer
  • expand [database name]> Programmability>Stored Procedures to verify that it's there
test stored procedure
Test Stored Procedure
  • click New Query and type the following:USE "database name"; 'within quotes GOEXECUTE [stored proc name] @[name] = [value], @[name2] = [value2];GO
    • ignore warnings
  • click !Execute button and verify results
sample code
Sample Code
  • uses Nations.mdf database from Classy Project
  • stored procedure is named dbo.GetByPopGolds

SELECT * from nations2

where Pop > @pop and Gold < @gold

add procedure to code
Add Procedure to Code
  • create a VB Project with a ListBox, two Textboxes and a Button
  • double click the Button to create a Sub and add code on following pages
declarations
Declarations

Dim sqc As SqlCommand

Dim da As SqlDataAdapter

Dim ds As DataSet

Dim con As SqlConnection

Dim dr As DataRow

Dim output As String

Dim pads() As Integer = {4, 22, 3, 3, 3, 7, 15}

Dim padDirection() As String = {"L", "R", "L", "L", "L", "L", "R"}

connection code
Connection Code

ds = New DataSet

con = New SqlConnection

con.ConnectionString ="server=.\SQLEXPRESS;" _

& AttachDbFilename=[path]\Nations.mdf;" _

& "Integrated Security=True;"

getting data
Getting Data

sqc = New SqlCommand

sqc.CommandText = "Exec [dbo].[GetByPopGolds] " _

& TextBox1.Text & ", " & TextBox2.Text

sqc.Connection = con

da = New SqlDataAdapter

da.SelectCommand = sqc

da.Fill(ds)

ListBox1.Sorted = True

load outputs
Load Outputs

For Each dr In ds.Tables(0).Rows

output = ""

For i As Integer = 0 To 6

If padDirection(i) = "L" Then

output += CStr(dr(i)).PadLeft(pads(i)) & " "

Else

output += CStr(dr(i)).PadRight(pads(i))

End If

Next

ListBox1.Items.Add(output)

Next

using optional parameters
Using Optional Parameters
  • Optional Parameters in SQL Stored Procedures
    • create a stored procedure based on the code at the above web page
    • right click the stored procedure name> Execute Stored Procedure>either check Pass Null Value or input the desired criterion (don't do both!), OK and review output
dynamic sql
Dynamic SQL
  • include SQL code in parameters rather than just values
    • potential security risks (SQL injection)
  • potential to create more flexible stored procedures
    • more options
microsoft access
Microsoft Access
  • can create stored procedures in VB code
  • http://www.devcity.net/Articles/18/msaccess_sp.aspx
creating a stored procedure
Creating a Stored Procedure
  • download sample.mdb into the project folder
  • create a query using design view
    • can also use query from form or report
      • see Help on saving SQL statements as queries
  • use Save As to save query within the database with the name you give it
additional activity
Additional Activity
  • try to modify code to run the Query that you created in the database
ad