SQL Server创建函数

标量函数，返回一个值

Use database_name;

GO

if object_id(N'dbo.function_name',N'IF') is not null

drop function dbo.function_name;

GO

create function dbo.function_name(@id int) returns varchar(180) //定义返回值的类型

AS

BEGIN

declare @name varchar(180); //定义返回值

select @name=table_colnum from table_name where id=@id

return @name;

END

使用函数:select dbo.function_name(@id)

表值函数，返回一个表

Use database_name;

GO

if object_id(N'dbo.function_name',N'IF') is not null

drop function dbo.function_name;

GO

create function dbo.function_name(@id int) returns table //定义返回数据为表

AS

return 

(

select * from table_name where id=@id

);

使用函数：select * from dbo.function_name(@id)

-- 截取指定分隔符直接字符串

USE [SourceDB]
GO
/****** Object:  UserDefinedFunction [dbo].[cut_func]    Script Date: 2015/1/8 17:20:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[cut_func] (@char varchar(100),@str varchar(10),@c int)
RETURNS varchar(10) AS
BEGIN

declare @var varchar(100);
declare @i int;
set @var=@char;
set @i=1;

while(@i <= @c)
BEGIN
set @var=substring(@var,charindex(@str,@var)+1,999);
set @i=@i+1;
END

set @var=left(@var,charindex(@str,@var)-1)

return @var
end