MIS 431 by pengxiang


									Start up
   Log on to the network
   Start Enterprise Manager
   Connect to Reliant\MIS431W04 and your
    SalesOrders database
   (from EM …) Start Query Analyzer
   Start Books Online
                          MIS 431
                         Dr. Steve Ross
                          Winter 2004

             Modifying Data, Views,
             and Stored Procedures

Material for this lecture is drawn from Guerrero and Rojas, SQL Server 2000 Programming,
                                 and the professor’s experience.
The INSERT Statement
   INSERT INTO table_name
       (field1, field2, … )
       VALUES (value1, value2, … )
   Null values
    •   Explicitly: NULL (not in quotes)
    •   Implicitly: not in field or value list
   Default values
    •   Explicitly: DEFAULT (not in quotes)
    •   Implicitly: not in field or value list
   Inserts a (new) single record
INSERT into Multiple Records
   INSERT INTO table_name
        SELECT field1,field2,…fieldn
        FROM other_table
   table_name must already exist
   SELECT statement fields must match
    table_name fields

   Use this form to add (a.k.a. append) data
    from one table to another
The SELECT INTO Statement
   SELECT field1, field2
       INTO table_name1
       FROM table_name2
       WHERE condition
   Creates and populates a table
   Often used to create a temporary table

   Use this form to create and add data from
    one table to another
Practical Exercise 13
   In your database …
    •   Change the specification of the EmployeeID field in
        Employees to Identity: yes
    •   Insert yourself as an employee
         • EmployeeID will be entered automatically – don’t try to
             enter it
         •   Look at an ER diagram for the table structure, enter all
             other fields using real data for your name and address,
             but incorrect data for area code and phone number
    • SELECT * FROM Employees
         • Make note of your EmployeeID, you’ll need it for the
             next Practical Exercise
The DELETE Statement
   DELETE table_name WHERE condition
   Removes rows (records)
   WHERE clause normally necessary
The UPDATE Statement
   UPDATE table_name
       SET field_name = value
       WHERE condition
   Changes field values
   WHERE clause normally necessary

   Used to change data in one or more records
    •   Use WHERE with primary key to update a specific
Practical Exercise 14
   In your database …
    •   Update your employee record, changing the area code
        and phone number to correct values
   “A view is basically a predefined query (a
    SELECT statement) that is stored in the
    database for later use.” (Guerrero & Rojas, p. 107)
   Benefits
    • Security layer
    • Partition the data
    • Combine data from many sources into one
      logical object
Creating Views
   CREATE VIEW statement
    •   Can reference up to 1024 columns
   Stored procedures
    •   sp_help
    •   sp_helptext
    •   sp_depends
Modifying Data in Views
   May update only one table at a time
    • View can be composed of fields from many
    •   Only fields in a single table can be modified
        by a given INSERT or UPDATE command

   May delete records in only one table
    • View can be composed of fields from only a
        single table
Practical Exercise 15
   In your database …
   Create a view (vueUserID) that returns SQL
    User IDs, first and last names, and person
    identification numbers of employees.
    •   Requires that you join Employees to UserIDs
    •   Use an outer join that shows all employees, even
        those who do not have a user ID
What Is a Stored Procedure?
   “… a database object that comprises
    one or more Transact-SQL statements.”
   “The main difference between a stored
    procedure and a set of statements is that
    a stored procedure can be reused just by
    calling its name.”
Benefits and Advantages
   Used to encapsulate or enforce business rules
   Precompiled statements – stored in memory
   Optimize network traffic – fewer lines of code sent to SQL
   Security mechanism – can grant execute privileges on SP
    and deny any other access to database
   Modular programming – run business logic as close to
    data as possible
   Auto-start possible – can be used to record or set-up
   Parameters – information can be sent to SP and also info
    can be returned from SP
Types of Stored Procedures
   System stored procedures
    •   sp_ prefix
    •   Admin tasks
    •   Available in all databases
   User-defined stored procedures
    •   Suggested prefix: usp_ (with or without _ )
    •   Unique name/owner within database
   Temporary stored procedures
    •   Prefix: # for local ## for global
   Extended stored procedures
    •   xp_ prefix
    •   Created and compiled in some other language
Creating Stored Procedures
   Created in local database

     Creation Time                                   Execution Time

            Parse                      Name Resolution               Optimization
    Check correct syntax                Check the existence      Choose an optimized
    and store information                   of all objects        execution plan and
      in sysobjects and                  referenced by the      store this information in
        syscomments                      stored procedure               memory

    Adaptation of Figure 8.1, Guerrero and Rojas, SQL Server 2000 Programming, p. 303
   Name must begin with @
   Declared at beginning:
             @parameter datatype(size)
             AS …
    •   Default value may be specified or NULL
                @parameter datatype(size) = value
                @parameter datatype(size) = NULL
   Input – used inside SP only
   Output – if so specified in parameter list and in
    call, output value can be assigned to a variable
Altering Stored Procedures
   Use ALTER PROCEDURE command or
    do it in Enterprise Manager
    • Keeps permissions intact (if you recreate SP,
        permissions are lost)
    •   Does not affect any dependent objects
    •   Does not affect auto-run property
   But …
    • Entire code must be included in ALTER …
    • ENCRYPTION option must be restated
The RETURN Statement
   Not required in simple SP’s but always
   Unconditional exit
    • At end of SP
    • Based on SP logic (e.g., IF … )
   Return value
    • Default 0 (zero) means all is well
    • Programmer can include other values to flag
Executing Stored Procedures
   In T-SQL and Query Analyzer:
      EXECUTE sp_name param
      EXECUTE @return_value = sp_name param
   Specifying input parameters
    • By name
       EXECUTE sp_name @param_name = value
    • By relative position
       EXECUTE sp_name value
                                       Continued …
Executing Stored Procedures                    cont’d

   Specifying output parameters
    • Before execution, a variable must be created
      to hold the output
       DECLARE @var_name datatype(size)
    • In EXECUTE statement, OUTPUT keyword
      must follow variable name, which relates to a
      specific parameter
       EXECUTE sp_name @var_name OUTPUT
Executing Stored Procedures in
Query Analyzer
   Option 1: enter the command
   Option 2: use the Object Browser
    • To see OB:
                Tools | Object Browser
    • Try it!
Recompilation of Stored
   Normally done after creating indexes or
    other things to optimize
   Three ways to force:
    • WITH RECOMPILE option in create or alter
    •   WITH RECOMPILE when executing SP
    •   sp_recompile stored procedure
Handling Errors
   @@ERROR system function
   RAISERROR command
    • Define error on the fly
    • User-created errors (sp_addmessage)
Nesting Stored Procedures
   Up to 32 levels
    • Although rare, a recursive procedure (calls
      itself) could hit this limit
   @@NESTLEVEL system function
Security Issues
   Best if
    • dbo owns SP and all referenced objects
      (e.g., tables, views)
   OK if
    • Same person owns SP and all referenced
   If dbo owns SP and all objects, users
    can be given execute privileges on SP
    but denied privileges on objects
Using Stored Procedures to
Return Data Sets in .ASP
   Create the stored procedure to include a
    SELECT statement or set of statements that
    mimic SELECT – returning a structured set of
   At beginning of SP: SET NOCOUNT ON
   At end of SP: SET NOCOUNT OFF
   In .ASP script:
      strParam = "value"
      strSQL = "exec dbo.usp_XYZ " & strParam
      set rsData = dbConn.Execute (strSQL)
  Next Lecture

     Triggers and
User-Defined Functions

To top