Chapter 13 Exercises

Exercise 1

Define a new view called vwSalesEmployees. It should return the EmployeeID, FirstName, LastName, and Title columns from the Employee table for all employees who are sales persons. Results should be sorted by last name and then by first name.

Answers

 CREATE VIEW vwSalesEmployees AS SELECT TOP 100 Percent EmployeeID , FirstName , LastName , Title FROM Employee WHERE SalesPersonFlag = 1 ORDER BY LastName, FirstName 

Exercise 2

Create a stored procedure called spEmployeeAddUpdate. This procedure accepts three parameters: FirstName, LastName, and LoginID. The data types are nVarChar(50), nVarChar(50), and nVarChar(256), respectively. Define logic in this procedure to check for an existing employee record with the same first and last name values. If the employee record exists, update the LoginID. Otherwise, insert a new employee record.

Answers

 CREATE PROCEDURE spEmployeeAddUpdate ( @FirstName   nVarChar(50) , @LastName    nVarChar(50) , @LoginID     nVarChar(256) ) AS IF EXISTS ( SELECT * FROM Employee  WHERE FirstName = @FirstName AND LastName = @LastName ) UPDATE Employee SET LoginID = @LoginID WHERE FirstName = @FirstName AND LastName = @LastName ELSE INSERT INTO Employee  (FirstName, LastName, LoginID)  SELECT @FirstName, @LastName, @LoginID 

Exercise 3

Add a comment header block and error handling logic to the procedure you created in exercise 2. The block should contain a description of the procedure, parameters, your contact information, the date created, and revision information.

Add error-handling logic to catch the errors that would occur if a parameter were omitted or if a null value were passed into a parameter. If an error is caught, raise a custom error message.

Answers

SQL Server 2000 error handling style:

 /************************************************* Adds or updates an employee record. If the first and last name match an existing record, updates the LoginID. 6-15-05 Paul Turley [e-mail] Revisions: 6-18-05 - Added comment block  and error handling. *************************************************/ CREATE PROCEDURE spEmployeeAddUpdate ( @FirstName   nVarChar(50) , @LastName    nVarChar(50) , @LoginID     nVarChar(256) ) AS IF EXISTS ( SELECT * FROM Employee  WHERE FirstName = @FirstName AND LastName = @LastName ) UPDATE Employee SET LoginID = @LoginID WHERE FirstName = @FirstName AND LastName = @LastName ELSE INSERT INTO Employee  (FirstName, LastName, LoginID)  SELECT @FirstName, @LastName, @LoginID IF @@ERROR IN (515, 2601) RAISERROR ‘FirstName, LastName & LoginID parameter values are required’, 16, 1 

SQL Server 2005 error handling style:

 /************************************************* Adds or updates an employee record. If the first and last name match an existing record, updates the LoginID. 6-15-05 Paul Turley [e-mail] Revisions: 6-18-05 - Added comment block  and error handling. *************************************************/ CREATE PROCEDURE spEmployeeAddUpdate ( @FirstName   nVarChar(50) , @LastName    nVarChar(50) , @LoginID     nVarChar(256) ) AS BEGIN TRY IF EXISTS ( SELECT * FROM Employee  WHERE FirstName = @FirstName AND LastName = @LastName ) UPDATE Employee SET LoginID = @LoginID WHERE FirstName = @FirstName AND LastName = @LastName ELSE INSERT INTO Employee  (FirstName, LastName, LoginID)  SELECT @FirstName, @LastName, @LoginID END TRY BEGIN CATCH IF @@ERROR IN (515, 2601) RAISERROR ‘FirstName, LastName & LoginID parameter values are required’ , 16, 1 ELSE RAISERROR ERROR_MESSAGE(), @ERROR, 16, 1 END CATCH 

Exercise 4

If you have completed all previous exercises, use the following script to produce a message. Analyze each statement so you understand how each value is produced.

 USE AdventureWorks2000 GO --  ALTER FUNCTION dbo.fnProperCase (@In VarChar(255))  RETURNS VarChar(255) AS BEGIN RETURN  UPPER(SUBSTRING(@In, 1, 1)) + LOWER(SUBSTRING(@In, 2, 254)) END GO -- Execute query from here if function has already been created –- -- Assemble message from string fragments: DECLARE @Message VarChar(255) SELECT @Message = dbo.fnProperCase(REVERSE(SUBSTRING(LastName, 4, 3))) FROM Employee WHERE EmployeeID = 199  SET @Message = @Message + ‘ ‘ + CHAR(73) + CHAR(39) + CHAR(109) + ‘ ‘ + CHAR(97) SELECT @Message = @Message + ‘ ‘ + dbo.fnProperCase(SUBSTRING(Description, 61, 5)) FROM ProductDescription WHERE ProductDescriptionID = 1586 SELECT @Message = @Message  + SUBSTRING(Name, 6, 3) FROM ProductSubCategory WHERE ProductSubCategoryID = 38 SELECT @Message = @Message + CHAR(45) + SUBSTRING(@@SERVICENAME, 3, 3) SELECT @Message = @Message  + ‘ ‘ + LOWER(SUBSTRING(Description, 1, 3)) FROM ProductDescription WHERE ProductDescriptionID = 847 SELECT @Message = @Message + SUBSTRING(LastName, 4, 3) FROM Employee WHERE EmployeeID = 21 SELECT @Message = @Message + CHAR(33) -- Print the entire string: PRINT @Message 

Answers

The message returned from this statement is:

Now I'm a Transact-SQL expert!