SQL Server/T-SQL/Store Procedure Function/Function Call

Pass table column to a user defined function

1>
2> CREATE TABLE EmployeeList
3>   (EmployeeName VarChar(100))
4> GO
1> INSERT INTO EmployeeList (EmployeeName) SELECT "Fred, First"
2> INSERT INTO EmployeeList (EmployeeName) SELECT "Rubble, Last"
3> GO
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE FUNCTION fnLastName (@Name VarChar(100))
4>   RETURNS VarChar(100)
5> AS
6>   BEGIN
7>      DECLARE @CommaPosition Int, @LastName varchar(100)
8>      SET @CommaPosition = 6
9>      SET @LastName = SUBSTRING(@Name, 1, @CommaPosition)
10>      RETURN @LastName
11>   END
12> GO
1>
2> SELECT dbo.fnLastName(EmployeeName) As LastName, dbo.fnLastName(EmployeeName) As FirstName
3> FROM EmployeeList
4> GO
LastName                                                                                             FirstName
---------------------------------------------------------------------------------------------------- -------------------
---------------------
Fred,                                                                                                Fred,
Rubble                                                                                               Rubble

(2 rows affected)
1>
2> drop function fnLastName
3> drop table EmployeeList
4> GO
1>

Select from a function returning a table

 

3>
4>
5> CREATE TABLE titles(
6>    title_id       varchar(20),
7>    title          varchar(80)       NOT NULL,
8>    type           char(12)          NOT NULL,
9>    pub_id         char(4)               NULL,
10>    price          money                 NULL,
11>    advance        money                 NULL,
12>    royalty        int                   NULL,
13>    ytd_sales      int                   NULL,
14>    notes          varchar(200)          NULL,
15>    pubdate        datetime          NOT NULL
16> )
17> GO
1>
2> insert titles values ("1", "Secrets",   "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The",       "business",     "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology",   "0736", $7.99,  $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology",   "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With",      "business",     "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley",    "mod_cook",     "0877", $19.99, $0.00,    12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?",      "trad_cook",    "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty",     "trad_cook",    "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> 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>
2> CREATE TABLE sales(
3>    stor_id        char(4)           NOT NULL,
4>    ord_num        varchar(20)       NOT NULL,
5>    ord_date       datetime          NOT NULL,
6>    qty            smallint          NOT NULL,
7>    payterms       varchar(12)       NOT NULL,
8>    title_id       varchar(80)
9> )
10> GO
1> insert sales values("1", "QA7442.3", "09/03/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482",    "09/04/94", 10, "Net 60",    "1")
3> insert sales values("3", "N914008",  "09/04/94", 20, "Net 30",    "2")
4> insert sales values("4", "N914014",  "09/04/94", 25, "Net 30",    "3")
5> insert sales values("5", "423LL922", "09/04/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/04/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE FUNCTION SalesByStore(@storid varchar(30))
4> RETURNS TABLE
5> AS
6> RETURN (SELECT title, qty
7>         FROM sales s, titles t
8>         WHERE s.stor_id = @storid AND t.title_id = s.title_id)
9> GO
1>
2> SELECT * FROM SalesByStore  ("8042")
3> GO
title                                                                            qty
-------------------------------------------------------------------------------- ------
(0 rows affected)
1>
2> drop FUNCTION SalesByStore;
3> GO
1>
2> drop table sales;
3> drop table titles;
4> GO
1>