SQL函数

--------创建内链表值函数(即:单语句)
ALTER function [dbo].[getUserByUserName]
(
@username varchar(20)
)
returns table --固定写法
as
return
(
--这里只能是一条查询语句
--select * from t_money where username=@username
select C.userid,C.username,(C.addmoney+C.money)as addmoney
from (select a.userid,a.username,a.[money],b.addmoney from t_money a,t_addmoney b
where a.userid=b.userid)C where username=@username
)

go
--调用:
select * from getUserByUserName('judy')

go

--------创建多语句表值函数
CREATE FUNCTION GetMangOfAddAfter
(
-- 参数声明
@AddMoney int
)
RETURNS

--声明一个table类型的表：@ReturnTable
@ReturnTable TABLE
(
--给@ReturnTable表指定都有哪些列
userid varchar(20),
username varchar(30),
[money] int
)
AS
BEGIN
-- 编写
insert into @ReturnTable (userid,username,[money])
select * from t_money
update @ReturnTable set [money]=[money]+@AddMoney

RETURN --固定写法
END

GO
--调用:
select * from GetMangOfAddAfter(100)
go

------新建标量值函数
CREATE FUNCTION dbo.getStrByName
(
-- 参数
@id int
)
RETURNS varchar(20)
AS
BEGIN
-- 声明变量
DECLARE @RetStr varchar(20),
@count int,@iNow int=0

-- 编写
select @count=count(*) from dbo.test where id=1
print @count
if(@count=0)
begin
set @RetStr=''
end
else
begin
while(@count>0)
begin
if(@iNow=0)
begin
select @RetStr=name from test
end
else
begin
set @RetStr=@RetStr+','
select @RetStr=name from test
end
end

set @iNow=@iNow+1
set @count=@count-1
end
-- 返回
RETURN @RetStr

END
GO

--调用：

select dbo.getStrByName(1)