Converting SQL server Bit fields to Yes/No values

Usually Yes/No values are saved in the database using bit data type. The problem with this kind of design is that when the table is bound a Visual Studio control, will show 0/1, instead of yes/no. For example, imagine the situation where programmer needs to save whether or not a user should be contacted, in the database and a repeater control will display that on a web page later on. The result can be something like the following:

Call me for more information: 0

Which would mean: do not contact the person. Obviously, it's not the best design; First of all it's not user-friendly at all and secondly, some people don't know 0 means false or no.

The following SQL server user-defined function can be used to do the conversion.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION dbo.CastBitToYesNo
(
@value bit
)
RETURNS varchar(20)
AS
BEGIN
Declare @ReturnValue varchar(20)

If @value=1
Set @ReturnValue='Yes'
Else
Set @ReturnValue='No'

RETURN @ReturnValue

END
GO

Here is an example of the use of the function:

Select CastBitToYesNo(MyBitField) as YesNoField from MyTable