SQL Server 2000 new features introduction 1

Create a user-defined function, freely use it directly in a Transaction SQL, just like a system function in your query statement. Create Function Syntax CREATE FUNCTION [OWNER_NAME] FUNCTION_NAME ([{@Parameter_name [as] scalar_parameter_data_type [= default]} [, ... n]])

Returns scalar_return_data_type

[With [[,] ... n]]

[As]

Begin function_body return scalar_expressionionend

Inline Table-Valued Functions

Create function [ooner_name.] Function_name ([{@Parameter_name [as] scalar_parameter_data_type [= default]} [, ... n]])

Returns Table

[With [[,] ... n]]

[As]

Return [(] Select-stmt [)]]

Multi-Statement Table-Valued Functions

Create function [ooner_name.] Function_name ([{@Parameter_name [as] scalar_parameter_data_type [= default]} [, ... n]])

Returns @return_variable table

[With [[,] ... n]]

[As]

Begin Function_Body Returnend

:: = {encryption | Schemabinding}

:: = ({colorn_definition | table_constraint} [, ... n])

Example

- Create a function crete function getValue () Returns Intas BeginReturn (SELECT Convert (int, value) as value from test where [name] = 'test') endGO

Select * from test2 where value = dbo.getValue ()

Go

Create Function GetTabletest (@ T1 DateTime, @ T2 DateTime) Returns Table As Return (SELECT T1.FIELD1, T2.Field1, T2.Qty, T3.field1, T3.field1 from Table1 T1 Inner Join Table2 T2 on T1.ID = T2 .Id inner join table3 t3 on t2.jid = t3.jid where t1.date> = @ T1 and t1.date <@ T2)

Go