关于SQL自定义函数的用法

括号里面是调用该函数时需要为函数提供的参数，比如你要根据学号查一个学生表，学号就是你定义函数的参数。returns 后面是返回值，这个语句里面返回的是一个自定义的表，其返回表里面应该包含MD001和MD003两列。当然还可以直接返回其他类型的数据，比如int，varchar等等…

Create Function RmGetPY(@chn nchar(1))

returns char(1)

as

begin

declare @n int

declare @c char(1)

set @n = 63

select @n = @n +1,@c = case chn when @chn then char(@n) else @c end from(

select top 27 from (

select chn =

'吖' union all select

'八' union all select

'嚓' union all select

'咑' union all select

'妸' union all select

'发' union all select

'旮' union all select

'铪' union all select

'丌' union all select

'丌' union all select

'咔' union all select

'垃' union all select

'呒' union all select

'拏' union all select

'噢' union all select

'妑' union all select

'七' union all select

'呥' union all select

'仨' union all select

'他' union all select

'屲' union all select

'屲' union all select

'屲' union all select

'夕' union all select

'丫' union all select

'帀' union all select @chn) as a

order by chn COLLATE Chinese_PRC_CI_AS

) as b

return(@c)

end

go

Create Function GetAllPY(@chn nvarchar(100))

returns varchar(30)

as

begin

declare @i int,@j int,@result varchar(100)

set @result=''

set @i=len(@chn)

set @j=1

while @j<=@i

begin

set @result = @result + dboRmGetPY(substring(@chn,@j,1))

set @j=@j+1

end

return @result

end

看看这两个，典型的取汉字拼音码的函数

GO

DECLARE @i INT

DECLARE @k INT

SET @k = 0

DECLARE @rtStr NVARCHAR(4000)

SET @str = @str + @sep

SET @i = CHARINDEX(@sep, @str)

WHILE @i > 0

BEGIN

--INSERT INTO @tb

--VALUES ( LEFT(@str, @i - 1) )

IF @k = @index

BEGIN

SET @rtStr = LEFT(@str, @i - 1)

BREAK

END

SET @str = SUBSTRING(@str, @i + 1, 4000)

SET @i = CHARINDEX(@sep, @str)

SET @k = @k + 1

END

SELECT RTRIM(LTRIM(@rtStr))

GO

你把这一段当成是一个SQL语句，放到程序里面去执行就是了

@str NVARCHAR(4000) ,

@sep NVARCHAR(4000) ,

@index INT

这几个东西用程序里面的string去代替就可以了

语句块也是一条SQL语句！！谢谢

函数如下：

create function f_os (@PARA int)

returns int as

begin

declare @ret int

if @PARA % 2 = 0

set @ret = @i

else

set @ret = -1

return @ret

end