Views and Stored Procedures

1
Views and Stored Procedures

• Peter DeBetta
• peterd_at_bluesand.com

http//www.bluesand.com
2
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

3
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

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

5
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

6
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

7
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

8
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
9
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
10
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

11
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

12
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

13
Stored Procedure Examples

• Return Recordset
• This example simply returns a recordset to the
  routine that made the call

14
Stored Procedure Examples

• Pass Parameters
• This example allows a single parameter to be
  passed into the procedure to limit the
  recordset results being returned

15
Stored Procedure Examples

• Optional Parameters
• The parameter is not required since a default
  value was specified

16
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

17
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

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

19
Where to Put them?
Called by ASP from ActiveX Server
Directly in ASP

• 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 VBs debugger
• Other applications can take advantage of ActiveX
  server

20
Whats Your Command?

• ADOs 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
21
Getting Started

• Create the Command
• Set its ActiveConnection
• Set the Stored Procedures name
• Add the parameters
• Name of the parameter
• Datatype of the parameter
• Length (if applicable)
• Input value (if applicable)
• Direction

22
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 functions return value)
23
Isnt 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
• Cant 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

24
Ill 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

25
A Simple Procedure

• CREATE PROC prUpdateAuthorContractsByState
• _at_state char(2),
• _at_contract bit
• AS
• UPDATE authors
• SET contract _at_contract
• WHERE state _at_state
• RETURN _at__at_ROWCOUNT

26
Setting up the Command

• Set objConn New ADODB.Connection
• strConn "ProviderSQLOLEDB Data
  SourceMyServer "
• strConn strConn "Initial Catalogpubs User
  Id" _ Session("UserName") " "
• strConn strConn " Password"
  Session("Password") ""
• objConn.Open strConn
• Set objCmd New ADODB.Command
• objCmd.CommandText "prUpdateAuthorContractsBySta
  te"
• objCmd.CommandType adCmdStoredProc
• Set objCmd.ActiveConnection objConn

27
Setting the Parameters

• objCmd.Parameters.Refresh
• objCmd.Parameters("_at_state") CA
• objCmd.Parameters("_at_contract") 1
• objCmd.Execute , , adExecuteNoRecords
• lngRows objCmd.Parameters("RETURN_VALUE").Value

28
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