Conditional statements I

Lecture 6

Stored procedures, functions and triggers

January 5, 2015

Conditional statements I

SQL Server provides two conditional statements: if..elseand case.

IF..ELSEstatement:

IF boolean_expression {statement1}

[

ELSE{statement2}

]

It is also possible to nest oneif..elsestatement inside of another.

Conditional statements II

Example (Sample if..else statements.) DECLARE @quarter int;

SELECT @quarter = DATEPART(quarter,GETDATE()) IF(@quarter=1)

PRINT 'The first quarter' ELSEIF(@quarter=2)

PRINT 'The second quarter' ELSEIF(@quarter=3)

PRINT 'The third quarter' ELSEPRINT 'The fourth quarter';

Conditional statements III

Thecasestatements has two forms. The rst forms is as follows:

CASE input_expression WHEN expr1 THEN result1 ...WHEN exprN THEN resultN [

ELSE else_result END]

Where:

input_expressionis any valid Microsoft SQL Server expression expr1,...,exprNare the value in which theinput_expression is compared,

Conditional statements IV

result1,...resultNare the value that will be return for theCASE statement if theexpr1,...,exprN evaluates to true, respectively, else_resultis the value that will be returned if none of

result1,...resultNevaluates to true

Example (Sample case statement  the rst form.) select first_name, last_name, case dept_id

WHEN 1 THEN 'She/he works at IT department' WHEN 2 THEN 'She/he works at HR department'

WHEN 3 THEN 'She/he works at Marketing department' ELSE 'No information'

ENDas 'Working place'

from Employment join Employee

on Employee.emp_id=Employment.emp_id

Conditional statements V

The second form of thecase statement is as follows:

CASEWHEN boolean_expr1 THEN result1 ...WHEN boolean_exprN THEN resultN [

ELSE expr END]

Where:

boolean_expr1,...,boolean_exprNis any Boolean expression that when it evaluates to truecase will return

result1,...,resultN, respectively.

Conditional statements VI

Example (Sample case statement  the second form) DECLARE @quarter int;

SELECT @quarter = DATEPART(quarter,GETDATE()) SELECT CASE

WHEN @quarter=1 THEN 'The first quarter' WHEN @quarter=2 THEN 'The second quarter' WHEN @quarter=3 THEN 'The third quarter' ELSE 'The fourth quarter'

END

Loops I

A loop enables to execute a block of statements a given number of times.

WHILE boolean_expression BEGIN

statements END

Example (Sample while statement) declare @counter int=10

while @counter>0 begin

print @counter

set @counter=@counter-1 end

Cursors I

Operations in a relational database act on a complete set of rows.

Some applications cannot always work eectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.

Cursors extend result processing by:

Allowing positioning at specic rows of the result set.

Retrieving one row or block of rows from the current position in the result set.

Supporting data modications to the rows at the current position in the result set.

Cursors II

Supporting dierent levels of visibility to changes made by other users to the database data that is presented in the result set.

Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.

The basic syntax to declare a cursor is as follows:

DECLARE cursor_name CURSOR FOR select_statement

Cursors III

Cursors are examined by means of the following statements:

OPEN cursor_name  it opens a cursor

FETCH [ NEXT | PRIOR | FIRST | LAST ] FROM cursor_name INTO @variable_name  it takes a specic row from a cursor. NEXT returns the row which is after the current row (it retrieves the rst row for the rst time). NEXT is the default. PRIORretrieves the row which is prior to the current row. FIRSTandLAST return the rst and last row, correspondingly.

@@FETCH_STATUS - it returns 0 if when FETCHis successful, otherwise returns -1. This variable allows to control a cursor inside theWHILE statement.

CLOSE cursor_name it closes a cursor.

DEALOCATE cursor_name it deletes a cursor reference.

Cursors IV

Example

DECLARE EmpDept_Cursor CURSOR

FOR SELECT e.emp_id,em.salary,d.name FROM Employee e JOIN Employment em ON

e.emp_id=em.emp_id JOIN Department d ON em.dept_id=d.dept_id

WHERE em.end_date IS NULL;

OPEN EmpDept_Cursor;

FETCH NEXT FROM EmpDept_Cursor;

WHILE @@FETCH_STATUS = 0 BEGIN

FETCH NEXT FROM EmpDept_Cursor;

END;CLOSE EmpDept_Cursor;

DEALLOCATE EmpDept_Cursor;

A stored procedure is a sequence of T-SQL statements kept on the server. SQL Server compiles the procedure code when it is invoked for the rst time. After compiling, an optimal execution plan for the procedure is stored in the cache.

There are several benets of using procedures:

Increasing security (users can execute procedures which operate on database objects to which they have no access).

Reusing the same code by many users or applications.

Increasing performance (subsequent procedure calls are performed on the basis of the execution plan which resides in the cache).

The basic syntax of theCREATE PROCEDUREstatement is as follows:

CREATE {PROC|PROCEDURE} procedure_name

[@parameter1 data_type, @parameter2 data_type,... ] AS sql_statements

TheALTER PROCEDUREandDROP PROCEDURE statements allow to modify and remove procedures, correspondingly.

ALTER {PROC|PROCEDURE} procedure_name

[@parameter1 data_type, @parameter2 data_type,...]

AS sql_statements

DROP {PROC|PROCEDURE} procedure_name

Example (Creating a procedure without parameters) CREATE PROCEDURE sp_current_employees

AS SELECT e.emp_id, e.first_name, e.last_name FROM Employee e JOIN Employment em ON

e.emp_id=em.emp_id

WHERE em.end_date IS NULL Example (Invoking the procedure) EXEC sp_current_employees

System stored procedures I

SQL Server provides many system stored procedures. They are mainly applied for administrative purposes.

Example (Invoking the system procedures) EXEC sp_who;

returns information about current users, sessions, and processes in a server instance

EXEC sys.sp_helpdb;

provides information about all databases

Procedures with parameters I

Procedures can take two kind of parameters:

input parameters - they allows to supply some values to the procedure.

output parameters - they allow to return some values to the procedure's caller.

Procedures with parameters II

Example (Creating and invoking a procedure with an input parameter.)

CREATE PROCEDURE GetEmpsFromDept

@dept varchar(40) AS

BEGIN

SELECT e.emp_id, e.first_name, e.last_name, em.salary

FROM Employee e JOIN Employment em ON e.emp_id=em.emp_id

JOIN Department d ON em.dept_id=d.dept_id WHERE em.end_date IS NULL AND d.name= @dept;

END;

EXEC GetEmpsFromDept 'IT'

Example (Creating and invoking a procedure with an input and output parameter.)

CREATE PROCEDURE GetWorkdaysByID

@id int, @workdays int OUTPUT ASBEGIN

SELECT @workdays =

DATEDIFF(day,start_date,GETDATE()) FROM Employment

WHERE emp_id = @id AND end_date IS NULL END;

DECLARE @id int = 1;

DECLARE @workdays int;

EXEC GetWorkdaysByID @id, @workdays OUTPUT;

SELECT @workdays;

A function is a sequence of T-SQL statements kept on the server.

It can take parameters and must return some value. SQL Server provides two types of user-dened functions:

scalar functions they return values of scalar data types (e.g.

integers, strings),

table-valued functions they return tables (e.g. the result of some select statement).

Scalar functions I

The basic syntax is as follows:

CREATE FUNCTION function_name

([@parameter1 data_type, @parameter2 data_type,...]) RETURNS data_type

[AS]BEGIN

T-SQL_statements

RETURN scalar_expression END

TheALTER FUNCTIONandDROP FUNCTION statements allow to modify and remove functions.

Scalar functions II

Example (Sample scalar function which takes one parameter.) CREATE FUNCTION GetNumberofEmps(@deptName varchar(30)) RETURNS INT

BEGIN

DECLARE @number int;

SELECT @number = COUNT(*)

FROM Employment em JOIN Department d ON em.dept_id=d.dept_id

WHERE d.name=@deptName;

RETURN @number;

END

Invoking the function

SELECT dbo.GetNumberofEmps('HR')

Table-valued functions I

There are two types of these functions:

inline table-valued functions (They involve only theRETURN clause followed by a single select statement).

multi-statement table-valued functions (They specify the structure of table variables which are returned by them. Their body can involve a sequence of T-SQL statements.)

Table-valued functions II

Inline table-valued functions The basic syntax is as follows:

CREATE FUNCTION function_name

([@parameter1 data_type, @parameter2 data_type,...]) RETURNS TABLE

[AS]RETURN

select_statement

Table-valued functions III

Example (Sample inline table-valued function with one parameter.) CREATE FUNCTION GetDeptsByAvgSal(@avg_salary money) RETURNS TABLE

AS RETURN

SELECT name

FROM Department d JOIN Employment em ON ON d.dept_id=em.dept_id

WHERE em.end_date IS NULL GROUP BY name

HAVING AVG(em.salary)>@avg_salary

Invoking the function

SELECT * FROM GetDeptsByAvgSal(2000)

Table-valued functions IV

Multi-statement table-valued functions The basic syntax is as follows:

CREATE FUNCTION function_name

([@parameter1 data_type, @parameter2 data_type,...]) RETURNS @table_variable TABLE table_defintion

[AS]BEGIN

T-SQL statements RETURN

END

Table-valued functions V

Example (Sample multi-statement table-valued function with one parameter.)

CREATE FUNCTION GetEmpsSalByDept(@deptName varchar(30)) RETURNS @emps TABLE

(emp_id int,

first_name varchar(30), last_name varchar(40), salary money)

ASBEGIN

INSERT INTO @emps

SELECT e.emp_id,first_name,last_name,salary

FROM Employee e JOIN Employment em ON e.emp_id=em.emp_id JOIN Department d ON em.dept_id=d.dept_id

WHERE d.name=@deptName;

RETURN END

Invoking the function

SELECT * FROM GetEmpsSalByDept('IT')

A trigger is a kind of stored procedure which SQL Server runs automatically when the proper even occurs. SQL provides three types of triggers:

DML triggers - they are run when users perform DML statements (INSERT,UPDATE,DELETE) on tables or views.

DDL triggers - they are run when users perform various DDL statements (e.g. CREATE,ALTER,DROP)

Logon triggers - they are run when users logon to the SQL Server instance to establish a session.

DML triggers I

There are types of DML triggers:

AFTER triggers - they are dened only on tables. SQL Server runs such a trigger when the proper DML statement has performed successfully.

INSTED OF triggers - they can be dened both tables and on views. SQL Server runs such a trigger instead of the proper DML statement.

The syntax is as follows:

CREATE TRIGGER trigger_name ON {table_name|view_name}

{AFTER|INSTEAD OF}

{[INSERT] [ , ] [UPDATE] [ , ] [DELETE]}

AS T-SQL_statements

DML triggers II

There are two special tables which can be used inside DML

triggers: INSERTED and DELETED. They are automatically managed by SQL Server. If a trigger runs in respond to anINSERTstatement the INSERTED table stores copies of rows which were added to the trigger table. In the case of anUPDATEstatements the INSERTED table stores copies of rows introduced by this statement. The DELETED table stores the old rows. If a trigger runs in respond to a DELETEstatement the DELETED table stores the rows which were removed from the trigger table.

DML triggers III

Example (Using a DML trigger to ensure that only adults people can work in the company.)

CREATE TRIGGER TR_Adults_Emps ON Employee

AFTER INSERT AS BEGIN IF EXISTS(SELECT 1 FROM inserted AS i

WHERE DATEDIFF(YEAR,i.birth_date,GETDATE())<18) BEGIN

PRINT 'An employee must be adult person';

ROLLBACK;

END;END;

DML triggers IV

Example (Test the trigger) insert into Employee

values('Martin','Cain','08.08.2000','M','MCAIN',null,null)

Logon triggers I

The basic syntax is as follows:

CREATE TRIGGER nazwa_wyzwalacza ON ALL SERVER

AFTER LOGON AS T-SQL_statements

Logon triggers II

Example (Using a logon trigger to prevent logon as Martin.) CREATE TRIGGER trgControlLogin

ON ALL SERVER FOR LOGON AS

BEGIN

IF(ORIGINAL_LOGIN()= 'Martin'

AND APP_NAME() = 'Microsoft SQL Server Management Studio')

ROLLBACK;

END;

DDL triggers I

The basic syntax is as follows:

CREATE TRIGGER trigger_name ON {ALL SERVER | DATABASE}

{AFTER} {event_type} [ ,...n ] AS

T-SQL_statements

ALL SERVER- the trigger scope involves the current instance.

DATABASE- the trigger scope involves the current database.

event_type- it denes DDL events (e.g. CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE PROCEDURE,ALTER PROCEDURE, CREATE INDEX, ALTER INDEX, etc.

DDL triggers II

Example (Using a DDL trigger to prevent modication or deletion of tables.)

CREATE TRIGGER trgPreventDDL_AD ON DATABASE

FOR ALTER_TABLE, DROP_TABLE ASBEGIN

PRINT 'Modification or deletion is not allowed';

ROLLBACK;

END;

Thank you for your attention!