Coalesce in Sql server example

Introduction

COALESCE() accepts a series of values and a value to use in the event that all items in the list are null; then, it returns the first not-null value. This tip describes two creative uses of the COALESCE() function in SQL Server.

Here is a simple example: You have a table of persons whose columns include FirstName, MiddleName and LastName. The table contains these values:

• John A. MacDonald
• Franklin D. Roosevelt
• Madonna
• Cher
• Mary Weilage

If you want to print their complete names as single strings, here's how to do it with COALESCE():

SELECT  FirstName + ' ' +COALESCE(MiddleName,'')+ ' ' +COALESCE(LastName,'')

If you don't want to write that for every query, Listing A shows how you can turn it into a function. Now whenever you need this script (regardless of what the columns are actually named) just call the function and pass the three columns. In the examples below, I'm passing literals, but you can substitute column names and achieve the same results:

SELECT dbo.WholeName('James',NULL,'Bond')
UNION
 SELECT dbo.WholeName('Cher',NULL,NULL)
UNION
 SELECT dbo.WholeName('John','F.','Kennedy')

Here is the result set:

Cher  
 James  Bond
 John F. Kennedy

You'll notice a hole in our thinking -- there are two spaces in James Bond's name. It's easy to fix this by changing the @result line to the following:

SELECT @Result = LTRIM(@first + ' ' + COALESCE(@middle,'') + ' ') + COALESCE(@last,'')

Here's another use of COALESCE(). In this example, I will produce a list of monies paid to employees. The problem is there are different payment arrangements for different employees (e.g., some employees are paid by the hour, by piece work, with a weekly salary, or by commission).

Listing B contains the code to create a sample table. Here are a few sample rows, one of each type:

1     18.0040    NULL  NULL  NULL  NULL
 2     NULL  NULL  4.00  400   NULL  NULL
 3     NULL  NULL  NULL  NULL  800.00      NULL
 4     NULL  NULL  NULL  NULL  500.00      600

Use the following code to list the amount paid to employees (regardless of how they are paid) in a single column:

SELECT 
      EmployeeID,
      COALESCE(HourlyWage * HoursPerWeek,0)+
      COALESCE(AmountPerPiece * PiecesThisWeek,0)+
      COALESCE(WeeklySalary + CommissionThisWeek,0)AS Payment
FROM [Coalesce_Demo].[PayDay]

Here is the result set:

EmployeeID  Payment
 1     720.00
 2     1600.00
 3     800.00
 4     1100.00

You might need that expression in several places in your application and, although it works, it isn't very graceful. This is how you can create a calculated column to do it:

ALTER TABLE Coalesce_Demo.PayDay
ADD Payment AS
      COALESCE(HourlyWage * HoursPerWeek,0)+
      COALESCE(AmountPerPiece * PiecesThisWeek,0)+
      COALESCE(WeeklySalary + CommissionThisWeek,0)

Now a simple SELECT * displays the pre-calculated results.

Summary

This tip demonstrates some unusual ways and places to apply the power of COALESCE(). In my experience, COALESCE() most often appears within a very specific content, such as in a query or view or stored procedure.

You can generalize the use of COALESCE() by placing it in a function. You can also optimize its performance and make its results constantly available by placing it in a calculated column.

Alternatives To SQL Server Cursors

Introduction

Anyone with more than a little exposure to SQL Server will soon come to the conclusion that the use of cursors is best avoided. SQL Server is a Relational Database Management System, and as such is very proficient at processing sets of data. The use of a cursor effectively repeats an operation once for each row being processed, thereby consuming far more server resources than would be necessitated by a set-based operation. In addition, since SQL cursors are processed much more slowly than set-based operations, any locks generated by the use of said cursor are held longer than would otherwise be necessary. In this way, the use of a cursor can affect not only the performance of a given batch of work, but also the performance of any other batches which need to make use of the same data resources.

In order to effectively avoid using a SQL cursor, one must know why a cursor is being used in the first place. The list below details the most common reasons why people use cursors, and some possible solutions for each problem.


Problem: The Developer or Designer is new to SQL Server
Problem: Need to update a table with incrementing column values for records already in the table
Problem: Need to use a custom identity function to perform an insert
Problem: Need to compare a row to adjacent rows in a table

Problem: The Developer or Designer is New to SQL Server

By far, this is the most common reason for cursor usage. If a programmer is already well versed in an event-driven programming language such as Visual Basic, they will possess a great wealth of experience in dealing with data items one at a time. As such, their natural inclination when moving into SQL is to do the same. Unfortunately, what is appropriate in one language may be (and in this case is) entirely inappropriate in another.

Resolution

The primary weapon against cursor usage in this scenario is to develop a strong understanding of the basics of Transact-SQL. There are countless courses, books, and websites dedicated to the topic. In addition, you can review the examples below, which present set-based solutions to problems which may otherwise necessitate a cursor.
If you’re still struggling with finding a set-based solution, post a question in the MSDN Transact-SQL Forum

Problem: Need to update a table with incrementing column values for records already in the table

This problem is most often encountered when importing data from an external data source. The external data source may not have a key field, for instance, and for one of various reasons, an identity column cannot be used while importing. 

Resolution

A number of methods may be employed to resolve this issue. The most efficient and straightforward method, available in SQL 2005 and greater, is to use theROW_NUMBER() function. 

Using the AdventureWorks database:

SELECT LoginID, ROW_NUMBER() OVER (ORDER BY LoginID ASC) AS RowKey
FROM  HumanResources.EmployeeLogins

WITH RowKeys (LoginID, RowKey) AS 
  (SELECT LoginID, 
    ROW_NUMBER() OVER (ORDER BY LoginID ASC) AS RowKey
  FROM HumanResources.EmployeeLogins)
UPDATE HumanResources.EmployeeLogins
SET  RowKey = rk.RowKey
FROM  HumanResources.EmployeeLogins el
JOIN  RowKeys rk ON el.LoginID = rk.LoginID

ROW_NUMBER() can also be used to number rows based upon logical groupings, or partitions in data, although this is less applicable to cursor replacement. For instance, the test data contains logins from multiple domains, and you may want to number the rows in the table based upon their position within a grouping of rows from the same domain.

--Use ROW_NUMBER in conjunction with PARTITION to create row numbers grouped by domain
SELECT LoginID, 
  ROW_NUMBER() OVER 
   (PARTITION BY LEFT(LoginID, CHARINDEX('\', LoginID) - 1) 
   ORDER BY LoginID ASC) AS RowKey
FROM  HumanResources.EmployeeLogins

Also worth noting is that the sort order of the OVER clause does not have to match the sort order of the outermost query. 
In the event that the numbering has to start at a number other than zero, that number can be added to ROW_NUMBER() in order to obtain the desired row numbers. 
For instance:

SELECT LoginID, 
  100 + ROW_NUMBER() OVER 
   (PARTITION BY LEFT(LoginID, CHARINDEX('\', LoginID) - 1) 
   ORDER BY LoginID ASC) AS RowKey
FROM  HumanResources.EmployeeLogins

The same function can be performed, albeit far less efficiently, in SQL 2000. The following solution uses an inline subquery in order to COUNT the number of rows on or before a given LoginID:

SELECT el1.LoginID,
  (SELECT COUNT(LoginID)
  FROM HumanResources.EmployeeLogins el2
  WHERE el2.LoginID <= el1.LoginID) AS RowKey
FROM HumanResources.EmployeeLogins el1

This solution is subject to error if duplicate values are present in the columns being used for comparison. If there were two identical LoginIDs, they would both be granted the same RowKey. When dealing with duplicate fields such as this, a local temporary table can be used in order to ensure unique row numbering:

CREATE TABLE #EmployeeLoginsTemp
 (RowKey int IDENTITY(1,1) NOT NULL,
 LoginID nvarchar(256))
 
INSERT #EmployeeLoginsTemp (LoginID)
SELECT LoginID FROM HumanResources.EmployeeLogins
 
SELECT * FROM #EmployeeLoginsTemp

Problem: Need to use a custom identity function to perform an insert

Many databases employ custom identity generation functions. There are VERY FEW good reasons for using custom identity functions in SQL Server, given the presence of IDENTITY columns and the UniqueIdentifier data type. That is not to say that you will never encounter such a situation - just that all possible alternatives should be vetted prior to deciding upon a custom identity solution.
A custom identity function commonly resembles the following:

CREATE TABLE Identities (TableName sysname PRIMARY KEY CLUSTERED,
      NextKey int NOT NULL)
 
CREATE PROC GetNextKey( @TableName sysname,
      @NextKey int OUTPUT)
AS
BEGIN
 --Fetch the NextID, if there is a row for the passed-in TableName in the Identities.
 SELECT @NextKey = NextKey
 FROM Identities
 WHERE TableName = @TableName
 
 --If there wasn't a row, @NextID will be NULL.
 IF @NextKey IS NULL
 BEGIN
  INSERT Identities (TableName, NextKey)
  SELECT @TableName, 1
 
  SELECT @NextKey = NextKey
  FROM Identities
  WHERE TableName = @TableName
 END
 
 --Advance the NextID value in Identities
 UPDATE Identities
 SET  NextKey = NextKey + 1
 WHERE TableName = @TableName
END

Resolution

The biggest problem with custom identity functions is that they serialize inserts. While the following solution (for SQL 2000 and greater) does not use a cursor, it does use a WHILE loop to call the GetNextKey proc once for each row in HumanResources.EmployeeLogins:

WHILE EXISTS (SELECT 1 FROM HumanResources.EmployeeLogins WHERE RowKey IS NULL)
BEGIN
 DECLARE @NextKey int
 EXEC dbo.GetNextKey 'HumanResources.EmployeeLogins', @NextKey OUTPUT
 
 UPDATE HumanResources.EmployeeLogins
 SET  RowKey = @NextKey
 WHERE LoginID = (SELECT TOP 1 LoginID FROM HumanResources.EmployeeLogins WHERE RowKey IS NULL)
END

Problem: Need to compare a row to adjacent rows in a table

A typical example of this type of problem is “I need to know by how much the latest price increase changed the price of each item”. The programmer often resorts to a cursor because they cannot understand how to compare each row with the previous row for the same item, where the previous row’s price was not equal to the current row’s price.

Resolution

In this situation, an inline query can be very useful. The following solution works for SQL 2000 and higher, and uses a different AdventureWorks table for illustration. In AdventureWorks, the Production.ProductListPriceHistory table stores pricing history for each item. The current row is identified by a NULL EndDate, so we must compare the current row to the most recent row for the same item.

SELECT plph1.ProductID,  plph1.ListPrice - 
  COALESCE((SELECT  TOP 1 ListPrice
    FROM  Production.ProductListPriceHistory plph2
    WHERE  EndDate IS NOT NULL
    AND   plph2.ProductID = plph1.ProductID
    ORDER BY EndDate DESC), 
    plph1.ListPrice) AS LastPriceChangeAmount
FROM Production.ProductListPriceHistory plph1
WHERE plph1.EndDate IS NULL

SQL Server Transactions and Error Handling

Transactions

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.

Users can group two or more Transact-SQL statements into a single transaction using the following statements:

• Begin Transaction
• Rollback Transaction
• Commit Transaction

If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.

Here is an example of a transaction :

USE pubs

DECLARE @intErrorCode INT

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

Before the real processing starts, the BEGIN TRAN statement notifies SQL Server to treat all of the following actions as a single transaction. It is followed by two UPDATE statements. If no errors occur during the updates, all changes are committed to the database when SQL Server processes the COMMIT TRAN statement, and finally the stored procedure finishes. If an error occurs during the updates, it is detected by if statements and execution is continued from the PROBLEM label. After displaying a message to the user, SQL Server rolls back any changes that occurred during processing. Note: Be sure to match BEGIN TRAN with either COMMIT or ROLLBACK.

Nested Transactions

SQL Server allows you to nest transactions. Basically, this feature means that a new transaction can start even though the previous one is not complete. Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. The @@TRANCOUNT automatic variable can be queried to determine the level of nesting - 0 indicates no nesting , 1 indicates nesting one level deep, and so fourth.
A COMMIT issued against any transaction except the outermost one doesn't commit any changes to disk - it merely decrements the@@TRANCOUNT automatic variable. A ROLLBACK, on the other hand, works regardless of the level at which it is issued, but rolls back all transactions, regardless of the nesting level. Though this is counterintuitive, there's a very good reason for it. If a nested COMMIT actually wrote changes permanently to disk, an outer ROLLBACK wouldn't be able to reverse those changes since they would already be recorded permanently.
When you explicitly begin a transaction, the @@TRANCOUNT automatic variable count increases from 0 to 1; when you COMMIT, the count decreases by one; when you ROLLBACK, the count is reduced to 0. As you see, the behavior of COMMIT and ROLLBACK is not symmetric. If you nest transactions, COMMIT always decreases the nesting level by 1, as you can see illustrated in Figure 1. The ROLLBACK command, on the other hand, rolls back the entire transaction, illustrated in Figure 2. This asymmetry between COMMIT and ROLLBACK is the key to handling errors in nested transactions.

Figure 1: A COMMIT always balances a BEGIN TRANSACTION by reducing the transaction count by one.

Figure 2: A single ROLLBACK always rolls back the entire transaction.

As you can see from Figure 1 and Figure 2, you can nest transactions and use the @@TRANCOUNT automatic variable to detect the level. You also learned that COMMIT and ROLLBACK do not behave symmetrically;COMMIT just decreases the value of @@TRANCOUNT, while ROLLBACK resets it to 0. The implication is that a transaction is never fully committed until the last COMMIT is issued. No matter how deeply you nest a set of transactions, only the last COMMIT has any effect.
Here is an example of a nested transaction :

USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0
BEGIN TRAN
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 1
    DELETE sales
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
                   -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
    COMMIT TRAN nested
                   -- Does nothing except decrement the value of @@TRANCOUNT

    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
                   -- The value of @@TRANCOUNT is 1
ROLLBACK TRAN

SELECT 'After ROLLBACK TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0 
-- because ROLLBACK TRAN always rolls back all transactions and sets 
-- @@TRANCOUNT to 0.

SELECT TOP 5 au_id FROM titleauthor

In this example we see that despite the nested COMMIT TRAN, the outer ROLLBACK still reverses the effects of the DELETE titleauthor command.
Here is another similar example of nested transaction :

USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0
BEGIN TRAN
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 1
    DELETE sales
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
               -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
    ROLLBACK TRAN
  
    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because 
    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT 
    -- to 0.

IF (@@TRANCOUNT > 0) BEGIN
    COMMIT TRAN -- Never makes it here cause of the ROLLBACK
    SELECT 'After COMMIT TRAN', @@TRANCOUNT
END

SELECT TOP 5 au_id FROM titleauthor

In this example, execution never reaches the out COMMIT TRAN because the ROLLBACK TRAN reverses all transactions currently in progress and sets @@TRANCOUNT to 0. Unless ROLLBACK TRAN is called with a save point, ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT to 0, regardless of the context in which it's called.

SAVE TRAN and Save Points

Savepoints offer a mechanism to roll back portions of transactions. A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. SQL Server allows you to use savepoints via the SAVE TRAN statement, which doesn't affect the @@TRANCOUNT value. A rollback to a savepoint (not a transaction) doesn't affect the value returned by @@TRANCOUNT, either. However, the rollback must explicitly name the savepoint: using ROLLBACK TRANwithout a specific name will always roll back the entire transaction.
The following script demonstrates how savepoints can be used :

USE pubs
SELECT 'Before BEGIN TRAN main', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 0

BEGIN TRAN main
    SELECT 'After BEGIN TRAN main', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 1
    DELETE sales
    SAVE TRAN sales  -- Mark a save point
    SELECT 'After SAVE TRAN sales', @@TRANCOUNT
   -- The value of @@TRANCOUNT is still 1

    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
        -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
        SAVE TRAN titleauthor  -- Mark a save point
        SELECT 'After SAVE TRAN titleauthor', @@TRANCOUNT
        -- The value of @@TRANCOUNT is still 2
    ROLLBACK TRAN sales

    SELECT 'After ROLLBACK TRAN sales', @@TRANCOUNT
     -- The value of @@TRANCOUNT is still 2

    SELECT TOP 5 au_id FROM titleauthor

IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN
    SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because 
    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT 
    -- to 0.
END
    
SELECT TOP 5 au_id FROM titleauthor

Error Handling

The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. The @@ERROR automatic variable is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of @@ERROR immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset to 0 when the next statement executes successfully. If a trappable error occurs, @@ERROR will have a value greater than 0. SQL Server resets the @@ERROR value after every successful command, so you must immediately capture the @@ERROR value. Most of the time, you'll want to test for changes in @@ERROR right after anyINSERT, UPDATE, or DELETE statement.

CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11), 
                          @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
    INSERT titles(title_id, title, type)
    VALUES (@title_id, @title, @title_type)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

    INSERT titleauthor(au_id, title_id)
    VALUES (@au_id, @title_id)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

COMMIT TRAN

RETURN 0

This kind of solution contains substantial repetition especially if your business logic requires more than two Transact-SQL statements to be implemented. A more elegant solution is to group codes into a generic error handling procedure:

CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
                          @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
    INSERT titles(title_id, title, type)
    VALUES (@title_id, @title, @title_type)

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

    INSERT titleauthor(au_id, title_id)
    VALUES (@au_id, @title_id)

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

COMMIT TRAN

RETURN 0

ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1

Transaction in SQL Server

A transaction must follows this properties:- 

In a perfect transaction world, a transaction must contain a series of properties known as ACID. These properties are: 

Atomicity 
A transaction is an atomic unit of work or collection of separate operations. So, a transaction succeeds and is committed to the database only when all the separate operations succeed. On the other hand, if any single operations fail during the transaction, everything will be considered as failed and must be rolled back if it is already taken place. Thus, Atomicity helps to avoid data inconsistencies in database by eliminating the chance of processing a part of operations only. 

Consistency 
A transaction must leave the database into a consistent state whether or not it is completed successfully. The data modified by the transaction must comply with all the constraints in order to maintain integrity. 

Isolation 
Every transaction has a well defined boundary. One transaction will never affect another transaction running at the same time. Data modifications made by one transaction must be isolated from the data modification made by all other transactions. A transaction sees data in the state as it was before the second transaction modification takes place or in the state as the second transaction completed, but under any circumstance a transaction can not be in any intermediate state. 

Durability 
If a transaction succeeds, the updates are stored in permanent media even if the database crashes immediately after the application performs a commit operation. Transaction logs are maintained so that the database can be restored to its original position before failure takes place. 

Using OUTPUT Parameters in Stored Procedures

One way to retrieve scalar data in addition to a standard resultset from a stored procedure is to use one or more output parameters. An output parameter is a parameter that is passed into the SQL stored procedure, but whose value can be set in the stored procedure. This assigned parameter, then, is readable back from the application that called the stored procedure.
To use an output parameter you need to indicate that the parameter is intended for output via the OUTPUT keyword. The following snippet shows a stored procedure that returns the set of inventory items through a SELECT statement and uses an output parameter to return the average price:

CREATE PROCEDURE store_GetInventoryWithAveragePrice
(
@AveragePrice money OUTPUT
)
AS
SET @AveragePrice = (SELECT AVG(Price) FROM store_Inventory)
SELECT InventoryID, ProductName, Price, UnitsOnStock
FROM store_Inventory

or

According to MS-BOL, SQL Server Stored-Procedures can return data in 4 forms:
1. Return Code: which are always an integer value.
2. OUTPUT Parameter: which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.

Specifying OUTPUT keyword to the parameters in the Stored Procedures can return the values of the parameters to the calling program. Lets check this with a simple example by using AdventureWorks database:

view source

print?

01	USE [AdventureWorks]

02

GO

03

04	--// Create Stored Prcedure with OUTPUT parameter

05	CREATE PROCEDURE getContactName

06	@ContactID INT,

07	@FirstName VARCHAR(50) OUTPUT,

08	@LastName  VARCHAR(50) OUTPUT

09

AS

10

BEGIN

11	SELECT @FirstName = FirstName, @LastName = LastName

12	FROM Person.Contact

13	WHERE ContactID = @ContactID

14

end

15

GO

16

17	--// Test the Procedure

18	DECLARE @CID INT, @FName VARCHAR(50), @LName VARCHAR(50)

19

20	--/ Test# 1

21	SET @CID = 100

22	EXEC getContactName @ContactID=@CID,

23	@FirstName=@FName OUTPUT,

24	@LastName=@LName OUTPUT

25

26	SELECT @FName as 'First Name', @LName as 'Last Name'

27	--/ Output

28	-- ContactID    First Name  Last Name

29	-- 100          Jackie      Blackwell

30

31	--/ Test# 2

32	SET @CID = 200

33	EXEC getContactName @ContactID=@CID,

34	@FirstName=@FName OUTPUT,

35	@LastName=@LName OUTPUT

36

37	SELECT @FName as 'First Name', @LName as 'Last Name'

38	--/ Output

39	-- ContactID    First Name  Last Name

40	-- 200          Martin      Chisholm

41

GO

42

43	--// Final Cleanup

44	DROP PROCEDURE getContactName

45

GO