表值函数笔记

表a中数据

id name num
13392 a 1
13393 b 2
13394 c 3
13395 d 4

新建表值函数为

if (object_id('ft', 'TF') is not null)  
   drop function ft  
go  

create function [dbo].[ft](@num int)
returns @t table(x int,x1 int,x2 nvarchar(50),y nvarchar(50),z int)
as 
begin
declare @x int
set @x=@num+1
insert into @t(x,x1,x2,y,z) select id,@x,name+'100',name,num from dbo.a where num>@x
return 
end
GO

--select * from ft(1)

结果为

x x1 x2 y z
13394 2 c100 c 3
13395 2 d100 d 4