Questions?
SQL Server Specialist Certificate Program
Maintaining SQL Server 2005
Week 4 – Creating Stored Procedures, Functions, and Triggers
Steve Stedman - Instructor
This Weeks Overview
● Review from Last Week + Homework● Class Project● Stored Procedures● Functions● System Stored Procedures● Triggers
● Review and Homework
Topics from last week
● Creating Tables● Implementing Constraints● XML
Homework
● Practice● Assignment● Reading
Class Project
● Status Update● Review of research and
experimentation required for the group project.
● Presentations will be week 9
Finding more Information
● When searching Google, search for ○ SQL Server 2005 _____○ TSQL _____
● This will help filter out other databases or sql languages.
● End of this section. Any Questions?
Preparation
We will be using the database created in week 3 for projects in class.We will also be using the AdventureWorks database.
1.Stored Procedures
● What are stored procedures?● What do you need to get started?● Writing your first stored procedure● Passing Parameters to stored
procedures● Output from stored procedures● Stored procedures in the SSMS UI
What are stored procedures?
● A name associated with a batch of SQL code, stored on the server
● Most-used programmatic structures in the database
● TSQL code is parsed once, and run many times for Stored Procedures.
What do you need to get started?
● Know what it is that you want to do in the procedure
● Know what the parameters are (if any)
● Know what needs to be returned● Figure out how to make it all work
Writing your first stored procedure
● A simple stored procedure can be created similar to how we created Views, first create a select statement, then save it as a procedure
CREATE PROCEDURE nameAS ● SELECT statement……
Sample Stored Procedure - Create
USE AdventureWorks; GO CREATE PROCEDURE Person.sp_who AS SELECT FirstName, LastName FROM Person.Contact;
Sample Stored Procedure - Execute
USE AdventureWorks; GO EXEC sp_who; EXEC Person.sp_who;
Sample Stored Procedure - Drop
USE AdventureWorks; GO DROP PROCEDURE Person.sp_who;
Passing Parameters to Stored Procedures
CREATE PROCEDURE spNewValue @pPKValue int, @pStatus char(8) AS
INSERT into MyTableSELECT Field1, Field2, 'Active' as StatusFrom MyTableWhere PKValue = @pPKValue
UPDATE MyTableSET Status = @pStatusWHERE PKValue = @pPKValue
Output from Stored Procedures
CREATE PROCEDURE [dbo].[uspLogError] @ErrorLogID [int] = 0 OUTPUTAS …BEGIN
SET @ErrorLogID = 7END
Stored Procedures in the SSMS UI
● Programmability..Stored Procedures● Look for
○ [HumanResources].[uspUpdateEmployeeLogin]
● Notice Try…Catch in stored procedure
Lab Project – Stored Procedures
● Create a stored procedure to wrap a SELECT statement
● Pass Data to the WHERE clause of a SELECT Stored Procedure
● Create a stored procedure to perform an insert statement using parameters
● Use the Week3 database or AdventureWorks
● End of this section. Any Questions?
1.Functions
● UDF = User Defined Functions
● Scalar Functions● Table Value / Rowset Functions● Deterministic / Non Deterministic● Built In Functions● Functions in the SSMS UI
Scalar Functions
● Accept 0 or more parameters● Return a single value● Similar to a simple function in most
any programming language
Table Value / Rowset Functions
● Similar to scalar, but return a table set
● Needs to have a table defined as the output
● See page 354-356
Deterministic / Non Deterministic
● Non Deterministic○ Different output given same input.○ Example Age_in_Seconds(‘7/21/1942’)
● Deterministic○ Returns same output for a given input
every time○ Can be indexed / clustered in a view
Built In Functions
● GETDATE()● DATEDIFF (datepart, startdate,
enddate ) ● SUBSTRING(value, start, length)● LEN(string_expression)● LOWER(string_expression)● @@SERVERNAME
Functions in the SSMS UI
● Programmability..Functions● Look for
○ [dbo].[ufnLeadingZeros]
● Notice RETURN (@ReturnValue);● SCHEMABINDING – improved
performance – prevents alter or drop on referenced tables and columns
Lab Project –
● Create a Scalar Function● Create a Table Value Function
● Run both execute both of these functions in the query editor
● End of this section. Any Questions?
● 10 Minute Break
3. System Stored Procedures● Administrative and informational activities
can be performed by using system stored procedures
● Example of sp_help on AdventureWorks○ EXEC sp_help '[Production].
[ProductProductPhoto]';
Lab Project
Use sp_help on the Week3 or AdventureWorks databases to find out information about one or more tables
Example: EXEC sp_help '[Production].[ProductProductPhoto]';
● End of this section. Any Questions?
4. Triggers
● Triggers Overview● DML triggers● After and Instead Of triggers● DDL triggers● Recursive and Nested triggers● Common uses of triggers
Triggers Overview
● Triggers are “Fired” on a specific event
● A useful addition to constraints● Data can be validated● See [AdventureWorks].[ddlDatabaseTriggerLog]
● Can cause code to be executed that may not be expected
● Show up in the query optimizer
DML triggers
● Attached to a specific table or view● Useful to track changes or log changes● See [HumanResources].[uDepartment]● INSERT and UPDATE
○ Could be used to update a “Last updated” column in the table
● DELETE○ Could be used to delete FK relations after the
row is deleted
After and Instead of Triggers
● AFTER triggers○ Run after the action is completed○ Default if INSTEAD of is not specified
● INSTEAD OF triggers○ Run instead of the action○ Useful for views – allows inserting to a
view
DML triggers example
CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee] INSTEAD OF DELETE AS BEGINSET NOCOUNT ON;DECLARE @DeleteCount int;SELECT @DeleteCount = COUNT(*) FROM deleted;IF @DeleteCount > 0 BEGINRAISERROR (N'Employees cannot be deleted. They can only be marked as not current.', 10, 1); -- Rollback any active or uncommittable transactionsIF @@TRANCOUNT > 0BEGINROLLBACK TRANSACTION;ENDEND;END;
DDL triggers
● Used to audit and regulate actions performed on the database
● Triggered on:○ Table creation, modification or drop○ Users are added (login added) or
dropped○ Databases created or dropped
DDL triggers example
Create TRIGGER trig_event_data ON DATABASE FOR CREATE_TABLE AS PRINT 'CREATE TABLE Issued.'select eventdata()RAISERROR ('New tables cannot be created in this database.', 16, 1) ROLLBACK;
Recursive and Nested triggers
● A trigger causing itself to fire is called recursion
● A trigger which modifies another table and causes a trigger to fire is called nested
● INSTEAD OF triggers do not fire recursively
● You can disable nested triggers at the server level
Common uses of triggers
● Enforce business rules● Validate input data● Write to other tables for audit trail
purposes● Query from other tables for cross-
referencing purposes● Access system functions (for example, print
an exception message when a rule is violated)
● Replicate data to different tables to achieve data consistency
Lab Project
● Using the AdventureWorks or Week3 databases:○ Create a DML trigger to prevent deleting
of a row in a table○ Create a DDL trigger to prevent the
creation of a table○ Create a DML trigger to convert a
varchar column to all caps (Use the UPPER function)
● End of this section. Any Questions?
Homework
● Homework for next week○ Handout