SQL SERVER 2000 new features introduction 1

Server
Create user-defined functions that are free to be used directly in a transaction SQL, like a system function in your query statement
Direct use.
CREATE FUNCTION
Grammar
CREATE FUNCTION [owner_name.] Function_name
([{@parameter_name [as] scalar_parameter_data_type [= default]} [,... N]])

RETURNS Scalar_return_data_type

[With < function_option> [[,] ... n]]

[As]

BEGIN
Function_body
Return scalar_expression
End

Inline table-valued functions

CREATE FUNCTION [owner_name.] Function_name
([{@parameter_name [as] scalar_parameter_data_type [= default]} [,... N]])

RETURNS TABLE

[With < Function_option > [[,] ... n]]

[As]

return [(] select-stmt [)]

Multi-statement table-valued functions

CREATE FUNCTION [owner_name.] Function_name
([{@parameter_name [as] scalar_parameter_data_type [= default]} [,... N]])

RETURNS @return_variable TABLE < table_type_definition >

[With < Function_option > [[,] ... n]]

[As]

BEGIN
Function_body
Return
End

< Function_option >:: =
{Encryption | Schemabinding}

< table_type_definition >:: =
({column_definition | table_constraint} [,... n])

Example


--Create a function
Create function GetValue ()
returns int
As
Begin
Return (select CONVERT (int,value) as Value from test where [name]= ' test ')
End
Go

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 on T3. Jid=t3. JID where T1. date>= @t1 and T1. date< @t2)

Go

SELECT * from Dbo.gettabletest (' 1999/1/1 ', ' 2001/2/1 ')