t-sql - 定义表值函数的例子

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE FUNCTION [dbo].[cs_innersys_GetObjectName]

(

-- Add the parameters for the function here

@TargetId NVARCHAR(50)

)

RETURNS

@Result TABLE 

(

-- Add the column definitions for the TABLE variable here

[Name] NVARCHAR(100)

)

AS

BEGIN

DECLARE @orgRowCount int

DECLARE @userRowCount int

declare @tempResult nvarchar(100)

select @orgRowCount = count(*) from dbo.Organizations where cast(@TargetId as uniqueidentifier) = Id

select @userRowCount = count(*) from dbo.cs_user_Infos where cast(@TargetId as uniqueidentifier) = UserId

if @orgRowCount > 0

begin

set @tempResult = (select [name] from dbo.Organizations where @TargetId = Id)

end

else if @userRowCount > 0

begin

set @tempResult = (select [RealName] from dbo.cs_user_Infos where @TargetId = UserId)

end

else

begin

set @tempResult = ''

end

--将数据插入临时表

INSERT INTO @Result([Name]) values(@tempResult)

RETURN

END