Inline Functions

for RuBoard

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):

Listing 10-3 A basic inline function.

 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.

for RuBoard