Home

Using Functions in Data Selection
 

Using a Built-In Function

To refine your data analysis, you can use functions, whether functions you create yourself or the Transact-SQL built-in functions. As mentioned previously, the first candidates of functions you should try to use are the built-in functions, some of which we reviewed.

To use a built-in function, in the placeholder of the column, type the name of the function, followed by its parentheses. If the function takes some parameters, remember to follow the rules of calling a parameterized function. Here is an example that uses some date-based built-in functions to display the ages of the students:

 
SELECT FirstName,
       LastName,
       Sex,
       DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
       City,
       State
FROM Students

This would produce:

Using Functions

You can also include a function in any of the operators we have reviewed so far. Here is an example:

SELECT FirstName, LastName, Gender, DateOfBirth, SPHome
FROM   Students
WHERE  (DateOfBirth BETWEEN CONVERT(DATETIME, '1995-01-01', 102) AND
			    CONVERT(DATETIME, '1999-12-31', 102))

This would produce:

Function

If you are working on a Windows application, you can assign the condition to the Filter property of the binding source.

Using a User-Defined Function

If none of the built-in functions satisfies your needs, you can create your own and use it during data analysis. Obviously, you should first create the function. Here is an example of two functions created in the ROSH database:

/* =============================================
   Author:      FunctionX
   Create date: Friday 6 April, 2007
   Description:	This function is used 
                to get the full name of a student
   =============================================*/
CREATE FUNCTION GetFullName
(
	@FName varchar(20),
	@LName varchar(20)
)
RETURNS varchar(41)
AS
BEGIN
	RETURN @LName + ', ' + @FName;
END;
GO
/* =============================================
   Author:	FunctionX
   Create date: Saturday 7 April, 2007
   Description:	This function is used 
                to display Yes or No
   ============================================= */
CREATE FUNCTION ShowYesOrNo
(
    @SPHomeStatus bit
)
RETURNS varchar(3)
AS
BEGIN
    DECLARE @Result varchar(3);

    IF @SPHomeStatus = 0
       SET @Result = 'No';
    ELSE
       SET @Result = 'Yes';

    RETURN @Result;
END;
GO

Once a function is ready, in the placeholder of your SQL statement, type dbo., followed by the name of the function, its parentheses, and its paremeter(s), if any, inside of the parentheses. Here is an example:

SELECT StudentID,
       dbo.GetFullName(FirstName, LastName) AS [Student's Name],
       Gender,
       dbo.ShowYesOrNo(SPHome) AS [Live's in a Single Parent Home?],
       ParentsNames AS [Parents' Names]
FROM Students;
GO

This would produce:

Function

 
 

Published on Friday 04 January 2008

 
Home Copyright © 2007 FunctionX, Inc.