Start up

1
Start up

• Log on to the network
• Start Management Studio
• Connect to Reliant\sql2k5 and your SalesOrders
  database
• Start Books Online

2
MIS 431Dr. Steve RossSpring 2007

• Views and Programmable Objects

Material for this lecture is drawn from SQL
Server 2005 Database Essentials, and the
professors experience.
3
The Set of Programmable Objects

• Views similar to programmable objects
• Stored Procedures
• User-defined Functions
• Database Triggers
• Assemblies
• Types
• Rules
• Defaults

4
Views and Programmable Objects

• Views

5
Views

• a view is a stored query that encapsulates
  the complexity of a query and presents the
  desired data to users or applications. (p. 179)
• Benefits
• Security layer
• Partition the data
• Combine data from many sources into one logical
  object

6
Creating Views

• CREATE VIEW statement
• Can reference up to 1024 columns
• WITH ENCRYPTION
• WITH SCHEMABINDING
• WITH CHECK OPTION
• Stored procedures
• sp_help
• sp_helptext
• sp_depends

7
Creating a View Using the Graphical Interface
8
Modifying Data in Views

• information on this slide is unverified for SQL
  Server 2005
• May update only one table at a time
• View can be composed of fields from many tables
• 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

9
Practical Exercise 16

• 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

10
Views and Programmable Objects

• T-SQL

11
Rudiments of T-SQL

• T-SQL Transact(ion) Structured Query Language
• Very Basic Stuff
• -- single line comment, can start anywhere in
  line
• / multi-line
• comment /
• DECLARE _at_variable_name type(size)
• SET _at_variable_name value
• SELECT _at_variable_name field FROM table WHERE
  condition

12
T-SQL in Management Studio
13
Rudiments of T-SQL

• Systems Functions of Interest
• _at__at_ERROR -- error code of last statement executed,
  if 0 (zero) no error
• _at__at_IDENTITY -- last identity value inserted in the
  current session
• _at__at_ROWCOUNT -- number of rows affected by last
  statement executed

14
Rudiments of T-SQL

• Control of Flow Statements
• IF condition
• do this if true -- allows only one line
  command here
• ELSE
• do this -- allows only one line command here
• IF condition
• BEGIN
• -- several statements can be here
• END
• ELSE
• BEGIN
• -- several statements can be here
• END

15
Rudiments of T-SQL

• Control of Flow Statements
• WHILE condition
• BEGIN
• -- several statements can be here
• IF condition
• CONTINUE -- goes back to the WHILE and
  re-evaluates
• IF condition
• BREAK -- exits the loop (to statement
  following END)
• END -- goes back to the WHILE and re-evaluates
• GOTO label -- continue execution at defined label
• label

16
Rudiments of T-SQL

• Program Flow Statements
• GO -- starts execution of preceding statements
• RETURN value -- stops execution, returns value
• WAITFOR DELAY
• WAITFOR TIME
• EXEC or EXECUTE -- executes a stored procedure

17
Practical Exercise 17

• Declare two variables UserName and
  UserNumber
• Assign the value Ann to UserName
• Using a select statement, assign the value of
  Anns UserID to UserNumber
• The UserName variable should be used in the
  criterion
• Display the value of the two variables

18
Views and Programmable Objects

• User-Defined Functions

19
What is a User-Defined Function?

• Three kinds
• Scalar returns a single value
• Inline table-valued returns a row x column set,
  like a view
• Multi-statement table-valued returns a row x
  column set, like a view
• Often used with parameters
• Constructed similar to a stored procedure

20
Scalar User-Defined Functions

• Can be used wherever an expression is accepted
• In SELECT clause
• In SET clause of UPDATE statement
• In FROM clause
• As a single-row single-column table
• As part of ON clause in a join
• In WHERE or HAVING condition
• In GROUP BY clause
• In ORDER BY clause as sorting criterion
• As a column DEFAULT
• Inside a CHECK constraint
• Inside a CASE expression
• In a PRINT statement
• As condition in IF or WHILE
• and more

21
Creating a Scalar UDF

• CREATE  FUNCTION owner_name.function_name     (
  _at_parameter_name AS scalar_parameter_data_type
  ,...n )
• RETURNS scalar_return_data_type
• AS
• BEGIN     function_body     RETURN
  scalar_expression
• END
• Typical Hungarian prefixes
• udf_ (with or without _ )
• fn_ (with or without _ )

22
Creating a Scalar UDF Using the Graphical
Interface
23
Invoking a Scalar UDF

• Use anywhere in any T-SQL statement in which an
  expression is allowed
• Must qualify the function name with its owner
  dbo.udf_XYZ
• If parameters are used, you must supply a value
  for every parameter, if not, empty parentheses
• Fields can be used as parameters

24
Practical Exercise 18

• Create a scalar UDF that takes a users first
  name as its parameter and returns that persons
  UserID
• Use the function in a SELECT statement to return
  a list of all persons and their UserIDs

25
Inline Table-ValuedUser-Defined Functions

• Can be used wherever a table or view is accepted
• In SELECT clause when it returns a single value
• In SET clause of an UPDATE statement when it
  returns a single value
• In FROM clause
• In WHERE or HAVING clauses when it returns a
  single value
• In WHERE clause as part of a subquery introduced
  by EXISTS or NOT EXISTS
• In WHERE or HAVING clause as part of subquery
  used with IN or NOT IN as long as it returns a
  single column

26
Creating an Inline Table-Valued UDF

• CREATE FUNCTION owner_name. function_name
      ( _at_parameter_name AS scalar_parameter_data_
  type ,...n )
• RETURNS TABLE
• AS
• RETURN ( SELECT-stmt )
• Has a single select statement following RETURN
• I use prefix tfn_ (or tfn)

27
Creating an Inline Table-Valued UDF Using the
Graphical Interface
28
Invoking an Inline Table-Valued UDF

• Use anywhere in any T-SQL statement where a table
  or view is allowed
• SELECT FROM dbo.udtProductsInCategory('bikes')
• Must qualify the function name with its owner
  dbo.udf_XYZ()
• If parameters are used, you must supply a value
  for every parameter, if not, empty parentheses
• Fields can be used as parameters
• For UPDATE, INSERT, DELETE, same conditions as
  doing these in a view

29
Practical Exercise 19

• Create an in-line table-valued UDF that takes a
  category description as its parameter and returns
  all products in that category
• Use the function in a SELECT statement to return
  a list of all products in the category
  Accessories

30
Multi-Statement Table-ValuedUser-Defined
Functions

• Can be used wherever a table or view is accepted
• In SELECT clause when it returns a single value
• In SET clause of an UPDATE statement when it
  returns a single value
• In FROM clause
• In WHERE or HAVING clauses when it returns a
  single value
• In WHERE clause as part of a subquery introduced
  by EXISTS or NOT EXISTS
• In WHERE or HAVING clause as part of subquery
  used with IN or NOT IN as long as it returns a
  single column
• Result is read-only

31
Creating a Multi-Statement Table-Valued UDF

• CREATE FUNCTION owner_name. function_name (
  _at_parameter_name AS scalar_parameter_data_type
  ,...n )
• RETURNS _at_return_variable TABLE lt
  table_type_definition gt
• AS
• BEGIN function_body RETURN
• END
• _at_return_variable must be defined like any table
• Function body includes an INSERT command to
  populate _at_return_variable

32
Invoking a Multi-Statement Table-Valued UDF

• Use anywhere in any T-SQL statement where a table
  or view is allowed
• Must qualify the function name with its owner
  dbo.udf_XYZ
• If parameters are used, you must supply a value
  for every parameter, if not, empty parentheses
• Fields can be used as parameters
• For UPDATE, INSERT, DELETE, same conditions as
  doing these in a view

33
Practical Exercise 20

• Create a multi-statement table-valued UDF that
  takes two parameters
• The first parameter is a first/last indicator
• if value 1 then last name first
• if value 0 then first name first
• The second parameter is a state abbreviation
• The function should return all customers in a
  given state, with names and city-state-zip
  concatenated.
• If no records from that state, so indicate.

34
Deterministic and Nondeterministic Functions

• Deterministic the function always returns the
  same value when invoked with the same arguments
• Only deterministic functions allowed when
• Creating a clustered index on a view
• Creating an index on a clustered column
• A UDF is deterministic when
• Every function referenced therein is
  deterministic
• Defined WITH SCHEMABINDING
• Does not reference objects not defined within the
  function itself

35
Security Implications of UDFs

• Permissions can be granted or denied
• Depending on type SELECT, UPDATE, INSERT,
  DELETE, REFERENCES, and EXECUTE
• If owner of function owns all objects referenced
  in the function, then user need be granted
  privileges on only the function, not the
  underlying objects
• Best practice DBO owns everything
• Best practice may not be practical in
  multi-developer environment

36
When to Use UDFs

• For commonly used formulas
• Formatting output
• Complex math
• Instead of stored procedures that return a single
  value
• Instead of a view that is read-only
• In constraints
• DEFAULT, CHECK, RULE, PRIMARY KEY, UNIQUE

37
User-Defined Functions in Access Projects

• SELECT tblFacAnnualData., dbo.fnSectionsInYear(P
  ersonID, LEFT(AcadYear, 4), 'A') AS AYSections
• FROM tblFacAnnualData
• ORDER BY AcadYear

38
Views and Programmable Objects

• Stored Procedures

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

40
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 Server
• 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 database
• Parameters information can be sent to SP and
  also info can be returned from SP

41
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

42
Creating Stored Procedures

• Created in local database

Adaptation of Figure 8.1, Guerrero and Rojas, SQL
Server 2000 Programming, p. 303
43
Parameters

• Name must begin with _at_
• Declared at beginning
• CREATE PROCEDURE dbo.uspXYZ _at_parameter
  datatype(size),
• _at_parameter datatype(size) OUTPUT AS
• Default value may be specified or
  NULL _at_parameter datatype(size)
  value _at_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

44
Altering Stored Procedures

• Use ALTER PROCEDURE command or modify in
  Management Studio
• 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

45
The RETURN Statement

• Not required in simple SPs but always
  recommended
• 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
  problems

46
Executing Stored Procedures

• In T-SQL and Management Studio
• EXECUTE sp_name paramEXECUTE _at_return_value
  sp_name param
• Specifying input parameters
• By name EXECUTE sp_name _at_param_name value
• By relative position EXECUTE sp_name value
• Continued

47
Executing Stored Procedures contd

• Specifying output parameters
• Before execution, a variable must be created to
  hold the output DECLARE _at_var_name datatype(size)
• Must be created as OUTPUT parameter in SP
• In EXECUTE statement, OUTPUT keyword must follow
  variable name, which relates to a specific
  parameter EXECUTE sp_name _at_var_name OUTPUT
• see example in Practical Exercise 22

48
Executing Stored Procedures in Management Studio

• In a new query
• EXEC dbo.uspXXX _at_param1value, _at_param2value
• GO

49
Recompilation of Stored Procedures

• Normally done after creating indexes or other
  things to optimize
• Three ways to force
• WITH RECOMPILE option in create or alter SP
• WITH RECOMPILE when executing SP
• sp_recompile stored procedure

50
Handling Errors

• _at__at_ERROR system function
• RAISERROR command
• Define error on the fly
• User-created errors (sp_addmessage)

51
Nesting Stored Procedures

• Up to 32 levels
• Although rare, a recursive procedure (calls
  itself) could hit this limit
• _at__at_NESTLEVEL system function

52
Security Issues

• Best if
• dbo owns SP and all referenced objects (e.g.,
  tables, views)
• OK if
• Same person owns SP and all referenced objects
• If dbo owns SP and all objects, users can be
  given execute privileges on SP but denied
  privileges on objects

53
Using Stored Procedures to Return Data Sets in
.ASP or Access

• Create the stored procedure to include a SELECT
  statement or set of statements that mimic SELECT
  returning a structured set of output
• 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)

54
Stored Procedures in Access Projects
55
Practical Exercise 21

• Create a stored procedure that takes two
  parameters
• The first parameter is a first/last indicator
• if value 1 then last name first
• if value 0 then first name first
• The second parameter is a state abbreviation
• The procedure should return all customers in a
  given state, with names and city-state-zip
  concatenated.
• If no records from that state, so indicate.

56
Practical Exercise 22

• Write a stored procedure that uses two
  parameters
• UserName as input
• UserNumber as output
• The procedure should return the user number of
  the person whose first name is input
• DECLARE _at_UserNum int, _at_UserName varchar(15)
• SET _at_UserName'ANN'
• EXEC dbo.uspNumberFromName _at_UserName, _at_UserNum
  OUTPUT
• SELECT _at_UserNum AS NumberOutput

57
Comparison of Stored Procedures and User-Defined
Functions
Table 9-1 from Microsoft SQL Server 2005
Database Essentials
58
Next Lecture

• Insert, Delete, and Update