Query a table returned from a function : function « Procedure Function « SQL Server / T-SQL Tutorial

2>
2>
3> CREATE TABLE authors(
4>    au_id          varchar(11),
5>    au_lname       varchar(40)       NOT NULL,
6>    au_fname       varchar(20)       NOT NULL,
7>    phone          char(12)          NOT NULL DEFAULT ('UNKNOWN'),
8>    address        varchar(40)           NULL,
9>    city           varchar(20)           NULL,
10>    state          char(2)               NULL,
11>    zip            char(5)               NULL,
12>    contract       bit               NOT NULL
13> )
14> GO
1> insert authors values('1',  'Joe',   'Abra',   '111 111-1111', '6 St.', 'Berkeley',  'CA', '11111', 1)
2> insert authors values('2',  'Jack',  'Majo',   '222 222-2222', '3 St.', 'Oakland' ,  'CA', '22222', 1)
3> insert authors values('3',  'Pink',  'Cherry', '333 333-3333', '5 Ln.', 'Vancouver', 'BC', '33333', 1)
4> insert authors values('4',  'Blue',  'Albert', '444 444-4444', '7 Av.', 'Vancouver', 'BC', '44444', 1)
5> insert authors values('5',  'Red',   'Anne',   '555 555-5555', '6 Av.', 'Regina',    'SK', '55555', 1)
6> insert authors values('6',  'Black', 'Michel', '666 666-6666', '3 Pl.', 'Regina',    'SK', '66666', 1)
7> insert authors values('7',  'White', 'Sylvia', '777 777-7777', '1 Pl.', 'Rockville', 'MD', '77777', 1)
8> insert authors values('8',  'Yellow','Heather','888 888-8888', '3 Pu',  'Vacaville', 'CA', '88888', 0)
9> insert authors values('9',  'Gold',  'Dep',    '999 999-9999', '5 Av.', 'Oakland',   'CA', '99999', 0)
10> insert authors values('10', 'Siler', 'Dean',   '000 000-0000', '4 Av.', 'Oakland',   'CA', '00000', 1)
11> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3>    CREATE FUNCTION dbo.fnAuthorList()
4>    RETURNS TABLE
5>    AS
6>    RETURN (SELECT au_id,
7>                au_lname + ', ' + au_fname AS au_name,
8>                address AS address1,
9>                city + ', ' + state + ' ' + zip AS address2
10>            FROM authors)
11>    GO
1>
2>    SELECT * FROM dbo.fnAuthorList()
3>    GO
au_id       au_name                                                        address1                                 address2
----------- -------------------------------------------------------------- ---------------------------------------- ------------------------------
1           Joe, Abra                                                      6 St.                                    Berkeley, CA 11111
2           Jack, Majo                                                     3 St.                                    Oakland, CA 22222
3           Pink, Cherry                                                   5 Ln.                                    Vancouver, BC 33333
4           Blue, Albert                                                   7 Av.                                    Vancouver, BC 44444
5           Red, Anne                                                      6 Av.                                    Regina, SK 55555
6           Black, Michel                                                  3 Pl.                                    Regina, SK 66666
7           White, Sylvia                                                  1 Pl.                                    Rockville, MD 77777
8           Yellow, Heather                                                3 Pu                                     Vacaville, CA 88888
9           Gold, Dep                                                      5 Av.                                    Oakland, CA 99999
10          Siler, Dean                                                    4 Av.                                    Oakland, CA 00000

(10 rows affected)
1>
2>    drop table authors;
3>    GO
1>    drop FUNCTION dbo.fnAuthorList;
2>    GO

21.2.function
	21.2.1.	The syntax for creating a multi-statement table-valued function
	21.2.2.	The syntax for creating a scalar-valued function
	21.2.3.	The syntax for creating a simple table-valued function
	21.2.4.	The syntax of the ALTER FUNCTION statement for a scalar valued function
	21.2.5.	The syntax for altering a simple table-valued function
	21.2.6.	The syntax for altering a multi-statement table-valued function
	21.2.7.	Call user-defined function in where clause
	21.2.8.	Call two user-defined functions in a select statement
	21.2.9.	Function WITH SCHEMABINDING
	21.2.10.	Multistatement Table-Valued Functions
	21.2.11.	Pass a declared variable to a function
	21.2.12.	Return TOP 100 PERCENT WITH TIES from a function
	21.2.13.	Get the 3 employees with the most RegionPlace
	21.2.14.	Use function as a view
	21.2.15.	Create a scalar-valued function that returns the total Billing amount due
	21.2.16.	Invoke the scalar-valued function
	21.2.17.	A SELECT statement that uses the function in a join operation
	21.2.18.	Use function to wrap a long sql statement
	21.2.19.	Query a table returned from a function