SQL SERVER标量函数定义

在用存储过程操作数据库中的数据时，往往会用到函数，以便在处理过程时更加方便、合理，还可以重复利用函数简化代码量

自定义函数有两种：表值函数：返回一个表

                          标量值函数：返回一个标量值

 具体函数结构如下：

 <1>表值函数

 create functiondbo.funTblTest[注：函数名]

(

[注:参数]

)

 returns @tmpTable[注:表格变量名] table

(

[注:表格变量定义]

)

 as

 begin

 [注:sql语句]

 end

 

实例讲析：通过部门Id查找其子级Id，可以是多级，并返回数据到表中

create function  funTbTest

(

@departId int

)

returns @tmpTbl table

(

rowId int identity primary key,

departId int,

departPid int,

departName varchar(50)

)

 as

 begin

declare @departPid int

declare @departName varchar(50)

 

select @departPid=departPid from TdepartmentInfo where departId=@departId

select @departName=departName from TdepartmentInfo wheredepartId=@departId

 

insert into @tmpTbl(departId,departPid,departName)values(departId,@departPid,@departName)

 

while exists(

select departId,departPid,departName fromTdepartmentInfo where departPid in

   (select departId from @tmpTbl ) and departId not in(select departId from @tmpTbl)

)

 

begin

insert into @tmpTbl  select departId,departPid,departName from TdepartmentInfo where departPid  in (select departId from @tmpTbl )

end

 

return

 

end

 

调用：select * from funTbTest(12)  

 <2>标量值函数

 

create function[函数名]

(

[参数]

)

 returns  [标量值类型]

 as

 begin

 [注:sql语句]

 end

 

实例讲析：通过部门Id查找其子级Id，可以是多级，并返回数据到表中

 

create function funStrTest

(

@depart_id int

)

returns int

 

as

begin

declare @tmpRtn int

select @tmpRtn=count(*)from TdepartmentInfo wheredepartPid=@depart_id

return @tmpRtn

end

 调用： select dbo.funStrTest(2)  注：dbo.一定不要忘记写 不然是调用不到函数的

 

 

USE [DBNAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <>
-- Create date: <>
-- Description: <>
-- =============================================

ALTER FUNCTION [dbo].[fn_NAME] (@PID INT)
RETURNS varchar(MAX)
AS
BEGIN
 DECLARE @rtStr varchar(MAX)
 SET @rtStr=''
  
 SELECT @rtStr = @rtStr + ISNULL(ColumnName,'')+ISNULL(ExtValue,'') + ' ' FROM TABLENAME1 cfg INNER JOIN TABLENAME2 ext
 ON cfg.ConfigID=ext.ConfigID
 WHERE PID =@PID
 
 RETURN @rtStr
END