Beginning Transact-SQL With SQL Server 2000 and 2005

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.

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

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.

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

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.

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 nospan@mydomain.com 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 nospam@mydomain.com 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

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

The message returned from this statement is:

Now I'm a Transact-SQL expert!