chapter 7 – part 3

Chapter 7 – Part 3Chapter 7 – Part 3

Stored Procedure, Stored Procedure, Function and TriggerFunction and Trigger

ObjectivesObjectives1. Database Programming2. Stored Procedure 3. Function4. Trigger

1. Database Programming1. Database Programming1.1 Variables1.2 Control-of-Flow Tools

1.1 Variables1.1 Variables Declare a variable:

DECLARE @limit moneyDECLARE @min_range int, @hi_range int

Assign a value into a variable: SET @min_range = 0, @hi_range = 100

SET @limit = $10 Assign a value into a variable in SQL

statement:SELECT @price = price FROM titles WHERE title_id = 'PC2091'

1.2 Control-of-Flow Tools1.2 Control-of-Flow Tools1.2.1 BEGIN…END1.2.2 IF…ELSE1.2.3 CASE … WHEN1.2.4 RETURN [n]1.2.5 WHILE1.2.6 PRINT

1.2.1 BEGIN…END1.2.1 BEGIN…END Defines a statement block Other Programming Languages:

C#, Java, C: { … }{ … } Pascal, Delphi: BEGIN … ENDBEGIN … END

1.2.2 IF…ELSE1.2.2 IF…ELSE Defines conditional and, optionally, alternate

execution when a condition is falseIF Boolean_expression IF Boolean_expression

T-SQL_statement | block_of_statements T-SQL_statement | block_of_statements [ELSE [ELSE

T-SQL_statement | block_of_statements ] T-SQL_statement | block_of_statements ] Example:IF (SELECT ytd_sales FROM titles WHERE title_id = 'PC10

35') > 5000 PRINT 'Year-to-date sales are

greater than $5,000 for PC1035.‘

1.2.3 CASE … WHEN1.2.3 CASE … WHENCASE input_expression

WHEN when_expression THEN result_expression [WHEN when_expression THEN result_expression…n] [ELSE else_result_expression ]

ENDExample:SELECT CASE payterms

WHEN 'Net 30' THEN 'Payable 30 days after invoice' WHEN 'Net 60' THEN 'Payable 60 days after invoice' WHEN 'On invoice' THEN 'Payable upon receipt of invoice' ELSE 'None'

END as Payment_Terms FROM sales ORDER BY payterms Other Programming LanguageC#, Java: Switch … Case Switch … Case ; VB: Select … CaseSelect … Case

1.2.4 RETURN [n]1.2.4 RETURN [n] Exits unconditionally of Trigger, Procedure or

Function and return a value (if any).

1.2.5 WHILE1.2.5 WHILE Repeats a statement (or block) while a specific

condition is trueWHILE Boolean_expression WHILE Boolean_expression SQL_statement | block_of_statementsSQL_statement | block_of_statements[BREAK] SQL_statement | block_of_statements [BREAK] SQL_statement | block_of_statements [CONTINUE][CONTINUE]

Example:WHILE (SELECT AVG(royalty) FROM roysched) < 25 BEGIN

UPDATE roysched SET royalty = royalty * 1.05 IF (SELECT MAX(royalty)FROM roysched) > 27 BREAK ELSE CONTINUE

ENDSELECT MAX(royalty) AS "MAX royalty" FROM roysched

1.2.6 PRINT1.2.6 PRINT Display message in SQL Query Analyze

(Console)PRINT stringPRINT string

Other Programming Languages: Java: System.out.print C#, VB.NET: Console.WriteLine

2. Stored Procedure2. Stored Procedure2.1 What Is a Stored Procedure?2.2 Stored Procedure vs. SQL Statement2.3 Create, update and delete a procedure

2.1 What Is a Stored Procedure?2.1 What Is a Stored Procedure? A stored procedure is a collection of T-SQL

statements that SQL Server compiles into a single execution plan.

Procedure is stored in cache area of memory when the stored procedure is first executed so that it can be used repeatedly. SQL Server does not have to recompile it every time the stored procedure is run.

It can accept input parameters, return output values as parameters, or return success or failure status messages.

2.2 Stored Procedure vs. SQL 2.2 Stored Procedure vs. SQL StatementStatement

First Time- Check syntax- Compile- Execute- Return data

Second Time- Check syntax- Compile- Execute- Return data

First Time- Execute- Return data

Second Time- Execute- Return data

SQL Statement Stored ProcedureCreating- Check syntax- Compile

2.3 Create, update and delete a 2.3 Create, update and delete a procedureprocedure

2.3.1 Create a Procedure2.3.2 Update a Procedure2.3.3 Delete a Procedure

2.3.1 Create a Procedure2.3.1 Create a Procedure2.3.1.1 Syntax2.3.1.2 Example 1 (Without parameters)2.3.1.3 Example 2 (With parameters)2.3.1.4 Example 3 (Using RETURN)

2.3.1.1 Syntax2.3.1.1 SyntaxCREATE PROC[EDURE] procedure_name

[ @parameter_name data_type] [= default] OUTPUT][,...,n]

AS T-SQL_statement(s)

2.3.1.2 Example 1 (Without 2.3.1.2 Example 1 (Without parameters)parameters)CREATE PROC Departments_Members

ASSELECT Dep_Name, COUNT(Emp_ID) NumberOfMemberFROM Departments D, Employees EWHERE D.Dep_ID = E.Dep_IDGROUP BY Dep_Name

Run ProcedureExecute Departments_Members

2.3.1.3 Example 2 (With 2.3.1.3 Example 2 (With parameters)parameters)CREATE PROC Department_Members @DeptName varchar(50)

ASSELECT Dep_Name, COUNT(Emp_ID) NumberOfMemberFROM Departments D, Employees EWHERE D.Dep_ID = E.Dep_ID and Dep_Name = @DeptNameGROUP BY Dep_Name

Run ProcedureExecute Department_Members ‘Accounting’

2.3.1.4 Example 3 (Using 2.3.1.4 Example 3 (Using RETURN )RETURN )CREATE PROC GROUPLEADER_MEMBERS @Emp_Code varchar(10) = null

ASIF @Emp_Code is nullBEGINPRINT 'Please enter Employee Code!'RETURNENDSELECT * FROM EmployeesWHERE EMP_EMP_ID = (SELECT EMP_ID FROM Employees WHERE Emp_Code = @Emp_Code)ORDER BY Emp_Name

2.3.2 Update a Procedure2.3.2 Update a ProcedureALTER PROC[EDURE] procedure_name

[ @parameter_name data_type] [= default] [OUTPUT]

[,...,n]AS

t-sql_statement(s)

2.3.3 Delete a Procedure2.3.3 Delete a Procedure DROP PROCEDURE procedure_name

3. 3. FunctionFunction3.1 What is a Function?3.2 Scalar functions Example3.3 Inline Table-valued Functions Example3.4 Multi-statement Table-Valued Functions

Example

3.1 What is a Function?3.1 What is a Function? See “What Is a Stored Procedure?” “What Is a Stored Procedure?” on slide 13 SQL Server supports three types of user-

defined functions: Scalar functions Inline table-valued functions Multi-statement table-valued functions

3.2 Scalar functions Example3.2 Scalar functions ExampleCREATE FUNCTION Revenue_Day (@Date datetime) Returns moneyASBEGIN

DECLARE @total moneySELECT @total = sum(sali_Quantity * sali_price)FROM Sales_Orders s, Sales_Orders_Items siWHERE s.sal_number = si.sal_number and year(sal_date) = year(@Date)

and month(sal_date) = month(@Date) and day(sal_date)= day(@Date)RETURN @total

END

Use:select dbo.Revenue_In_Day(GETDATE())

3.3 Inline Table-valued Functions 3.3 Inline Table-valued Functions ExampleExampleCREATE FUNCTION AveragePricebyItems (@price money = 0.0) RETURNS table

AS RETURN ( SELECT Ite_Description, Ite_Price

FROM Items WHERE Ite_Price > @price)

Use:select * from AveragePricebyItems (15.00)

3.4 Multi-statement Table-Valued 3.4 Multi-statement Table-Valued Functions ExampleFunctions Example

CREATE FUNCTION AveragePricebyItems2 (@price money = 0.0) RETURNS @table table (Description varchar(50) null, Price money null) AS begin insert @table SELECT Ite_Description, Ite_Price

FROM Items WHERE Ite_Price > @price

return end

Use:select * from AveragePricebyItems2 (15.00)

4. 4. TriggerTrigger4.1 What is a Trigger?4.2 Creating Syntax4.3 Disable/Enable syntax4.4 Deleted and Inserted tables4.5 Example4.6 Other Functions

4.1 What is a Trigger?4.1 What is a Trigger? A trigger is a special type of stored procedure that

is executed automatically as part of a data modification.

A trigger is created on a table and associated with one or more actions linked with a data modification (INSERT, UPDATE, or DELETE).

When one of the actions for which the trigger is defined occurs, the trigger fires automatically

Following are some examples of trigger uses: Maintenance of duplicate and derived data Complex column constraints Cascading referential integrity Complex defaults Inter-database referential integrity

4.2 Creating Syntax4.2 Creating SyntaxCREATE TRIGGER trigger_name

ON <tablename><{FOR | AFTER}>{[DELETE] [,] [INSERT] [,] [UPDATE]}

AS SQL_Statement [...n]

4.3 Disable/Enable syntax4.3 Disable/Enable syntax Disable syntax

Disable trigger <trigger_name> on <table_name>

Enable syntaxEnable trigger <trigger_name> on <table_name>

4.4 Deleted and Inserted tables4.4 Deleted and Inserted tables When you create a trigger, you have access to two

temporary tables (the deleted and inserted tables). They are referred to as tables, but they are different from true database tables. They are stored in memory—not on disk.

When the insert, update or delete statement is executed. All data will be copied into these tables with the same structure.

The values in the inserted and deleted tables are accessible only within the trigger. Once the trigger is completed, these tables are no longer accessible.

Inserted Table

Deleted Table

new new old old

Insert Update Delete

4.5 Example4.5 ExampleCREATE TRIGGER Print_Update

ON Bicycle_InventoryFOR UPDATE

ASPRINT "The Bicycle_Inventory table was

updated"

4.6 Other Functions4.6 Other Functions View contents of trigger

sp_helptext <trigger name> View number of triggers of a table

sp_helptrigger <table name>

chapter 7 – part 3

Documents