sql新建函数_创建函数sql

Create FUNCTION RiverTree(
//----------可以传入参数
)
RETURNS @SelectedTree TABLE
(
    id varchar(50),
    parentId varchar(50)
)
AS
BEGIN

//-----下边是游标查询------开始
declare mycursor cursor 
for SELECT name from zssl_river
open mycursor 
declare @pname varchar(20) 
fetch next from mycursor into @pname 
while @@fetch_status=0 
begin 

INSERT into @SelectedTree(id,parentId
) SELECT 。。。。。(查询的两个字段语句)

fetch next from mycursor into @pname 
end 
close mycursor 
deallocate mycursor

//-----------游标查询-----------结束
RETURN
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

因为返回的是table，所以sql查询的时候需要

SELECT * from dbo.RiverTree()
1

如果是简单的返回int之类的，查询语句

SELECT dbo.RiverTree()
1