Faculty of Computing and Informatics
Lesson 2 - Practical
Programming in Advanced SQL
Adapted from: Modern Database Management 13th Edition
Learning Objectives
After studying this lesson, you should be able to:
• Write single and multiple table queries using SQL commands.
• Write queries to create dynamic and materialised views.
• Understand common uses of database triggers and stored
procedures.
NOTE
Practicals are depended on the theory session.
Lecturer must execute the practical after the theory
session. The practical Lecturers will be notified to
start with a practical. This will maintain consistency
and quality amongst the various practical groups.
Students need to understand the theoretical
background before they can apply the theory into
practical.
Conditional Expressions – Part 1
• Syntax:
Conditional Expressions – Part 2
Conditional Expressions-Part 3
WHILE LOOP • DECLARE @s_value INT;
• SET @s_value = 0;
Syntax •
WHILE condition • WHILE @s_value <= 10
BEGIN • BEGIN
•
{...statements...} PRINT 'Inside WHILE LOOP on
END; Prog.com';
• SET @s_value = @s_value + 1;
• END;
•
•PRINT 'Done WHILE LOOP on
Prog.com';
• GO
Triggers – Part 1
Triggers – Part 2
Triggers – Part 3
Triggers – Part 4
Procedures and Functions – Part 1
• Syntax:
•
Procedures and Functions – Part 2
•
Procedures and Functions – Part 3
• Procedures
(stored procedure with an output Parameter)
Create or Replace procedure sp_CountContacts_By_Title
@Title nvarchar(8), @TitleCount int= 0 output
//The keyword output marks it as an output parameter
As
select Contact.Title, Contact.FirstName, Contact.LastName
from Person.Contact
where Contact.Title = @Title
Select @TitleCount = count(*)
from Person.Contact
where Title=@Title
return @TitleCount
Procedures and Functions – Part 4
• Test stored procedure
Declare @return_value int,
@TitleCount int
Execute
@return_value=sp_CountContacts_By_Title
@Title='Mr.',
@TitleCount=@TitleCount output
Select 'Total Title Count' =@return_value
Procedures and Functions – Part 5
• Executing a Procedure
Declare @return_value int,
@TitleCount int
Execute
@return_value=sp_CountContacts_By_Title
@Title='Mr.',
@TitleCount=@TitleCount output
Select 'Total Title Count' =@return_value
Procedures and Functions – Part 6
• Modifying a Procedure
ALTER procedure sp_Select_All_PersonContact
As
SELECT Contact.Title, Contact.FirstName,
Contact.LastName
FROM Person.Contact
ORDER BY Contact.LastName
Procedures and Functions – Part 7
• Execute sp_helptext
'sp_Select_All_PersonContact’
• Renaming Stored Procedures
sp_rename 'sp_Select_All_PersonContact’,
'sp_Select_All_ContactDetails’
• Deleting Stored Procedures
Drop procedure sp_Select_All_ContactDetails
Procedures and Functions-Part 8
• You can use IF and ELSE • You can use CASE expression to
commands to control the flow compare the results of an
of commands. expression with a series of tests
IF <some condition> and return a result when the test
returns true.
then <some command>
SELECT JobTitle,
ELSE <some condition>
CASE Gender
then <some command>
WHEN ‘M’ THEN ‘Male’
• You can use loops to process
multiple records. WHEN ‘F’ THEN ‘Female’
DECLARE @cnt INT = 0; ELSE ‘Unknown Value’
WHILE @cnt <cnt_total END
BEGIN {… statements …}
SET @cnt = @cnt +1; END
Parameters – Part 1
create procedure getSalesperson @sp varchar(25)
as select SalesPerson, Mon, amount from SalesData where SalesPerson = @sp; Go
declare @sp varchar(25) set @sp = 'Jack' exec getSalesperson @sp
create procedure getSalesperson_bad @sp varchar(25)
as declare @sql varchar(max) set @sql = ' select SalesPerson, Mon, amount
from SalesData where SalesPerson = ''' + @sp + ''';' exec (@sql) Go
declare @sql nvarchar(4000)
declare @monthNo int
declare @minAmount decimal set @sql = N' select SalesPerson
from dbo.SalesData where mon = @MonthNo and amount > @minAmount'
set @monthNo = 2 set @minAmount = 100
exec sp_executesql @sql, N'@monthNo int, @minAmount decimal', @monthNo, @minAmount
Parameters – Part 2
GO -- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO -- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819; SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO -- Try to use a function as a parameter value. -- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
GO -- Passing the function value as a variable.
DECLARE @CheckDate datetime; SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; GO
USE AdventureWorks2012; GO IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.uspGetSalesYTD; GO CREATE PROCEDURE Sales.us
Parameters – Part 3
USE AdventureWorks2012;
GO IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.uspGetSalesYTD; GO
CREATE PROCEDURE Sales.uspGetSalesYTD
@SalesPerson nvarchar(50) = NULL -- NULL default value
AS SET NOCOUNT ON;
-- Validate the @SalesPerson parameter.
IF @SalesPerson IS NULL BEGIN
PRINT 'ERROR: You must specify the last name of the sales person.'
RETURN END
-- Get the sales for the specified sales person and -- assign it to the output parameter.
SELECT SalesYTD FROM Sales.SalesPerson
AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson; RETURN GO
Data Dictionary Facilities – Part 1
Data Dictionary Facilities – Part 2
