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