Function WITH SCHEMABINDING : function « Procedure Function « SQL Server / T-SQL Tutorial






  1. SQL Server / T-SQL Tutorial
  2. Procedure Function
  3. function
4> CREATE TABLE titles(
5>    title_id       varchar(20),
6>    title          varchar(80)       NOT NULL,
7>    type           char(12)          NOT NULL,
8>    pub_id         char(4)               NULL,
9>    price          money                 NULL,
10>    advance        money                 NULL,
11>    royalty        int                   NULL,
12>    ytd_sales      int                   NULL,
13>    notes          varchar(200)          NULL,
14>    pubdate        datetime          NOT NULL
15> )
16> 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 dbo.sales s, dbo.titles t
8>         WHERE s.stor_id = @storid AND t.title_id = s.title_id)
9> GO
1>
2>
3> ALTER FUNCTION SalesByStore(@storid varchar(30))
4> RETURNS TABLE
5> WITH SCHEMABINDING
6> AS
7> RETURN (SELECT title, qty
8>         FROM dbo.sales s, dbo.titles t
9>         WHERE s.stor_id = @storid AND t.title_id = s.title_id)
10> GO
1>
2>
3> SELECT obj_name = SUBSTRING(OBJECT_NAME(d.id), 1, 20),
4>        dep_obj  = SUBSTRING(OBJECT_NAME(d.depid), 1, 20),
5>        col_name = SUBSTRING(name, 1, 15),
6>        IsSchemaBound = CASE deptype
7>            WHEN 1 THEN 'Schema Bound'
8>            ELSE 'Free'
9>        END
10> FROM
11>     sysdepends d
12>     JOIN syscolumns c ON d.depid = c.id
13>     AND d.depnumber = c.colid
14> WHERE object_name(d.id) LIKE 'SalesByStore%'
15> GO
obj_name             dep_obj              col_name        IsSchemaBound
-------------------- -------------------- --------------- -------------
SalesByStore         titles               title_id        Schema Bound
SalesByStore         titles               title           Schema Bound
SalesByStore         sales                stor_id         Schema Bound
SalesByStore         sales                qty             Schema Bound
SalesByStore         sales                title_id        Schema Bound

(5 rows affected)
1>
2>
3>
4> SELECT routine_definition
5> FROM INFORMATION_SCHEMA.routines
6> WHERE routine_name = 'SalesByStore'
7> GO
routine_definition




















--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



CREATE FUNCTION SalesByStore(@storid varchar(30))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (SELECT title, qty
        FROM dbo.sales s, dbo.titles t
        WHERE s.stor_id = @storid AND t.title_id = s.title_id)
(1 rows affected)
1>
2>
3>
4> drop FUNCTION SalesByStore;
5> GO
1> drop table sales;
2> drop table titles;
3> GO
1>








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