Inline functions are also quite powerful. They provide a "parameterized view" type of functionality; something many of us have been requesting of Microsoft for years. An inline table-valued function has no body, so there''s no BEGIN…END requirement with it. It consists only of a SELECT query, happily ensconced in a RETURN statement. Because the SELECT defines what type of table is actually returned, the RETURNS clause of an inline function simply lists TABLE as the return type, with no accompanying table definition. This differs from regular table-valued functions in which the entire table definition must be included in the RETURNS clause.
Here''s an example that demonstrates an inline function (Listing 10-3):
CREATE TABLE tempdb..singles (band int, single int, title varchar(30)) INSERT tempdb..singles VALUES(0,0,''LITTLE BIT O'''' LOVE'') INSERT tempdb..singles VALUES(0,1,''FIRE AND WATER'') INSERT tempdb..singles VALUES(0,2,''ALL RIGHT NOW'') INSERT tempdb..singles VALUES(1,0,''BAD COMPANY'') INSERT tempdb..singles VALUES(1,1,''SHOOTING STAR'') INSERT tempdb..singles VALUES(1,2,''FEEL LIKE MAKIN'''' LOVE'') INSERT tempdb..singles VALUES(1,3,''ROCK AND ROLL FANTASY'') INSERT tempdb..singles VALUES(1,4,''BURNING SKY'') INSERT tempdb..singles VALUES(2,0,''SATISFACTION GUARANTEED'') INSERT tempdb..singles VALUES(2,1,''RADIOACTIVE'') INSERT tempdb..singles VALUES(2,2,''MONEY CAN''''T BUY'') INSERT tempdb..singles VALUES(2,3,''TOGETHER'') INSERT tempdb..singles VALUES(3,0,''GOOD MORNING LITTLE SCHOOLGIRL'') INSERT tempdb..singles VALUES(3,1,''HOOCHIE-COOCHIE MAN'') INSERT tempdb..singles VALUES(3,2,''MUDDY WATER BLUES'') INSERT tempdb..singles VALUES(3,3,''THE HUNTER'') GO DROP FUNCTION PaulRodgersSingles GO CREATE FUNCTION PaulRodgersSingles(@title varchar(50)=''%'') RETURNS TABLE AS RETURN(SELECT Free=MIN(CASE band WHEN 0 THEN CAST(title AS char(18)) ELSE NULL END), BadCompany=MIN(CASE band WHEN 1 THEN CAST(title AS char(21)) ELSE NULL END), TheFirm=MIN(CASE band WHEN 2 THEN CAST(title AS char(23)) ELSE NULL END), Solo=MIN(CASE band WHEN 3 THEN title ELSE NULL END) FROM tempdb..singles WHERE title LIKE @title GROUP BY single) GO SELECT * FROM PaulRodgersSingles(DEFAULT)
(Results abridged)
Free BadCompany TheFirm Solo ------------------ --------------------- ----------------------- ------------------------------ LITTLE BIT O'' LOVE BAD COMPANY SATISFACTION GUARANTEED GOOD MORNING LITTLE SCHOOLGIRL FIRE AND WATER SHOOTING STAR RADIOACTIVE HOOCHIE-COOCHIE MAN ALL RIGHT NOW FEEL LIKE MAKIN'' LOVE MONEY CAN''T BUY MUDDY WATER BLUES NULL ROCK AND ROLL FANTASY TOGETHER THE HUNTER NULL BURNING SKY NULL NULL
This routine takes a linear data setthe singles tableand produces a cross tab that''s broken out by band. The function takes single parameter, @title, that can be specified to limit the rows returned by the cross tab. In this sense, the function operates as a kind of parameterized view.