views and stored procedures l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Views and Stored Procedures PowerPoint Presentation
Download Presentation
Views and Stored Procedures

Loading in 2 Seconds...

play fullscreen
1 / 28

Views and Stored Procedures - PowerPoint PPT Presentation


  • 153 Views
  • Uploaded on

Views and Stored Procedures. Peter DeBetta peterd@bluesand.com. http://www.bluesand.com. Objectives. To learn how to create views and stored procedures in SQL Server 7.0 To demonstrate how to implement ADO to leverage the power of stored procedures. Agenda. Views Overview

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

Views and 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.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
views and stored procedures

Views and Stored Procedures

Peter DeBetta

peterd@bluesand.com

http://www.bluesand.com

objectives
Objectives
  • To learn how to create views and stored procedures in SQL Server 7.0
  • To demonstrate how to implement ADO to leverage the power of stored procedures
agenda
Agenda
  • Views Overview
  • Creating Stored Procedures
  • Advantages and Disadvantages of Views and Stored Procedures
  • Implementing ADO Code to Utilize Views
  • Writing ADO Code to Call Stored Procedures
who is this guy
Who is this Guy?
  • Experience
    • 5 years of n-tiered experience
    • e-Commerce and mission critical business solutions
  • Professionally Certified
    • MCP and MCT
  • Proven Knowledge
    • Author
      • MIND Magazine
      • VBPJ Magazine
      • SQL Server Programming Unleashed
      • Professional ADO/RDS with ASP
    • Trainer/Speaker
    • VB, ADO, SQL Server, IIS, ASP ...
views overview
Views Overview
  • Predefined SQL Statement
  • Can include a single table or multiple joined tables
  • Useful for reporting
  • Can help to simplify more complex SQL statements
  • The Order By clause may not be used
views overview6
Views Overview
  • Updating via views
    • Updatable when opened in a recordset object
    • Cannot be updated if aggregate query
    • Calculated fields cannot be updated
  • Updating multiple table views
    • Can be updated
    • Fields from only one table may be updated at a time
view examples
View Examples
  • Views allow multiple table joins to be more easily referenced
    • Retrieve using SELECT * FROM vwAuthorsAndTitles
    • The view can be joined to another table or view
view examples8
View Examples
  • Views allow you to create aggregate queries
    • Calling SELECT * FROM vwSalesTotals is a lot easier than calling the SQL statement shown below

CREATE VIEW vwSalesTotals

AS

SELECT title_id,

SUM(qty) as BooksSold

FROM sales

GROUP BY title_id

view examples9
View Examples
  • Views allow you to alias column names to a more use-friendly convention

CREATE VIEW vwAuthorPhoneList

AS

SELECT au_lname AS LastName,

au_fname AS FirstName,

phone AS PhoneNumber

FROM authors

stored procedures
Stored Procedures
  • Once created,
    • Precompiled
    • Pre-syntax checked
  • Cached using a Least Recently Used (LRU) algorithm
    • Whenever a procedure is called, it is placed at the top of the list
    • Procedures at the bottom of the list are removed if space is needed
    • While in cache, no recompiling takes place
  • Better performance than other SQL objects
stored procedures11
Stored Procedures
  • Can return recordset(s) or Execute SQL
    • Return records using a Select statement
    • Modify records using Insert, Update or Delete statements
  • Can accept, modify and return parameter values
    • All datatypes supported except text and image
    • OUTPUT clause allows for modification of parameter values
    • A return value may also be sent to the client
stored procedures12
Stored Procedures
  • More than just a SQL statement
    • Written in T-SQL, allowing more complex logic than a view
    • Control of Flow, Looping, Variables
  • Can use temp tables
    • Hold values for processing
    • Return as a recordset to the client
    • Can help simplify more complex SQL statement
stored procedure examples
Stored Procedure Examples
  • Return Recordset
    • This example simply returns a recordset to the routine that made the call
stored procedure examples14
Stored Procedure Examples
  • Pass Parameters
    • This example allows a single parameter to be passed into the procedure to limit the recordset results being returned
stored procedure examples15
Stored Procedure Examples
  • Optional Parameters
    • The parameter is not required since a default value was specified
stored procedure examples16
Stored Procedure Examples
  • Update Data
    • This example will update an authors first and last names based on the id that was passed into the procedure
stored procedure examples17
Stored Procedure Examples
  • Output parameters
    • Although this example does return values, it uses the existing parameters rather than incurring the cost of returning a recordset
in steps asp and ado
In Steps ASP and ADO
  • Stored Procedures can be run directly from ASP through ADO code
  • Speeds up application, especially for often used procedures
  • Removes complex SQL from application code
  • Modularizes distinct functionality in stored procedures
    • Reusability
    • Encapsulation
  • 2 methods of running stored procedures in with ADO
    • Through ASP directly in server-side script
    • Through a server-side ActiveX server (i.e.. one created from VB)
where to put them
Code is all in one place (per ASP page)

Easier to test?

Difficult to maintain

Not very scalable

ADO code is spread out across an entire Web project of ASPs

Scalable solution

Reusable code

ADO code is encapsulated

Easier to maintain

Easier to test using VB’s debugger

Other applications can take advantage of ActiveX server

Where to Put them?

Called by ASP from ActiveX Server

Directly in ASP

what s your command
What’s Your Command?
  • ADO’s Command object can be used to execute stored procedures in Oracle, SQL Server, Access (queries)
  • Command approximates to Stored Procedure (SQL Server)
  • Collection of Parameter objects

Command

Parameters

Parameter

getting started
Getting Started
  • Create the Command
  • Set its “ActiveConnection”
  • Set the Stored Procedure’s name
  • Add the parameters
    • Name of the parameter
    • Datatype of the parameter
    • Length (if applicable)
    • Input value (if applicable)
    • Direction
point me in the right direction
Point Me in the Right Direction

ActiveX server

Database

Input

Send value in to the stored procedure

Output

Retrieve value from the stored procedure

Input/Output

Send value into and retrieve value back from the stored procedure

Stored Procedure

Return Values

Retrieve numeric value from the stored procedure (like a function’s return value)

isn t that refreshing
Isn’t that Refreshing!
  • “Refresh“ method asks SQL Server to create the ADO Parameter objects and fill in their data
  • Can take longer than creating them yourself (usually negligible)
  • Easier
  • Much less code
  • Does not always work w/ SQL Server 6.5 through OLE DB
  • Can’t always determine the direction accurately
    • SQL Server interprets all Output parameters as InputOutput
  • Puts parameters in same order as they exist in the stored procedure
i ll do it myself
I’ll Do it Myself
  • “CreateParameter” method
  • Need to know the:
    • name
    • datatype
    • datatype length
    • parameter direction
  • Faster
  • Much more code
  • Need to add them in the correct order
a simple procedure
A Simple Procedure

CREATE PROC prUpdateAuthorContractsByState

@state char(2),

@contract bit

AS

UPDATE authors

SET contract = @contract

WHERE state = @state

RETURN @@ROWCOUNT

setting up the command
Setting up the Command

Set objConn = New ADODB.Connection

strConn = "Provider=SQLOLEDB; Data Source=MyServer; "

strConn = strConn & "Initial Catalog=pubs; User Id=" & _ Session("UserName") & "; "

strConn = strConn & " Password=" & Session("Password") & ";"

objConn.Open strConn

Set objCmd = New ADODB.Command

objCmd.CommandText = "prUpdateAuthorContractsByState"

objCmd.CommandType = adCmdStoredProc

Set objCmd.ActiveConnection = objConn

setting the parameters
Setting the Parameters

objCmd.Parameters.Refresh

objCmd.Parameters("@state") = “CA”

objCmd.Parameters("@contract") = 1

objCmd.Execute , , adExecuteNoRecords

lngRows = objCmd.Parameters("RETURN_VALUE").Value

summary
Summary
  • Creating Views
  • Creating Stored Procedures
  • Advantages and Disadvantages of Views and Stored Procedures
  • Implementing ADO Code to Utilize Views
  • Writing ADO Code to Call Stored Procedures