1 / 28

Views and Stored Procedures

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

yon
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. 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. Views and Stored Procedures Peter DeBetta peterd@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. 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

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

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

  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 function’s return value)

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

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

  25. A Simple Procedure CREATE PROC prUpdateAuthorContractsByState @state char(2), @contract bit AS UPDATE authors SET contract = @contract WHERE state = @state RETURN @@ROWCOUNT

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

  27. Setting the Parameters objCmd.Parameters.Refresh objCmd.Parameters("@state") = “CA” objCmd.Parameters("@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

More Related