Funcs & Procs - END -- Use the ShortName and...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Mote’s Notes 3/23/2011 -- Define function to concatenate first and last names (John Doe) CREATE FUNCTION FullName ( @FN Varchar(Max), @LN Varchar(Max) ) RETURNS Varchar(Max) BEGIN RETURN @FN + ' ' + @LN END -- Use the FullName function SELECT dbo.FullName(FirstName, LastName) AS Employee FROM tblEmployee SELECT dbo.FullName(E.FirstName, E.LastName) AS Employee, dbo.FullName(F.FirstName, F.LastName) AS Family FROM tblEmployee E LEFT OUTER JOIN tblFamily F ON E.EID=F.EID -- Define function to concatenate first initial and last name (J. Doe) CREATE FUNCTION ShortName ( @FN Varchar(Max), @LN Varchar(Max) ) RETURNS Varchar(Max) BEGIN RETURN Left(@FN,1) + '. ' + @LN END -- Define function to determine size of EID's family CREATE FUNCTION FamilySize ( @EID Varchar(20) ) RETURNS Int BEGIN RETURN ( SELECT COUNT(FID) FROM tblFamily WHERE EID=@EID )
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: END -- Use the ShortName and FamilySize functions SELECT dbo.ShortName(FirstName, LastName) AS Employee, dbo.FamilySize(EID) AS Size FROM tblEmployee ORDER BY LastName, FirstName-- Procedure to give all current employees a 5% raise CREATE PROC spPayRaise AS BEGIN UPDATE tblEmployee SET AnnualSalary=ROUND(1.05*AnnualSalary,2) WHERE Active='Y' END -- Procedure to give all current employees an X dollar raise CREATE PROC spPayRaise2 @X Money AS BEGIN UPDATE tblEmployee SET AnnualSalary=AnnualSalary + @X WHERE Active='Y' END -- Procedure to remove an employee, his family and his paychecks CREATE PROC spDiscardEmployee @EID Varchar(20) AS BEGIN DELETE tblPaycheck WHERE EID=@EID DELETE tblFamily WHERE EID=@EID DELETE tblEmployee WHERE EID=@EID END -- Use the stored procedures EXEC spPayRaise EXEC spPayRaise2 2500.00 EXEC spDiscardEmployee 'MT26' EXEC spDiscardEmployee 'OMP' EXEC spDiscardEmployee 'JMote'...
View Full Document

Page1 / 2

Funcs & Procs - END -- Use the ShortName and...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online