SQLSERVER2000新增功能介绍１

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

建立用户自定义函数，可以自由的在一个TransactionSQL中直接使用，就像一个系统函数一样在你的查询语句中
直接使用。
CREATEFUNCTION
语法
CREATE FUNCTION[owner_name.]function_name
   ([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])

RETURNSscalar_return_data_type

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

[AS]

BEGIN
   function_body
   RETURNscalar_expression
END

InlineTable-valuedFunctions

CREATEFUNCTION[owner_name.]function_name
   ([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])

RETURNSTABLE

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

[AS]

RETURN[(]select-stmt[)]

Multi-statementTable-valuedFunctions

CREATEFUNCTION[owner_name.]function_name
   ([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])

RETURNS@return_variableTABLE<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])

示例

--创建函数
create functiongetValue()
returnsint
as
begin
return(selectconvert(int,Value)ASValuefromTestwhere [Name]='Test')
end
go

select*fromTest2whereValue=dbo.getValue()

go

create functiongetTabletest(@t1datetime,@t2datetime)
returnstable
as
 return(selectt1.Field1,t2.Field1,t2.Qty,t3.Field1,t3.Field1
 fromTable1t1innerjoin Table2t2ont1.ID=t2.IDinnerjoinTable3t3ont2.JID=t3.JIDwheret1.Date>=@t1andt1.Date<@t2)

go

select*fromdbo.getTabletest('1999/1/1','2001/2/1')

 

 

 

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>