Custom SQL function numbers include metric indicators

 

1 -- input a string parameter and return a string
2 create function pmselect (@ pproid nvarchar (40 ))
3 returns nvarchar (50)
4 begin
5 declare @ plist nvarchar (50)
6 declare @ PM nvarchar (10)
7 -- Define a cursor
8 declare pm_cursor cursor
9 select personname from tblperson where oId in (select user_id from tblpromanager where pro_id = @ pproid)
10
11 -- open the cursor and save the retrieved content to @ pm
12 open pm_cursor
13 fetch next from pm_cursor into @ pm
14 set @ plist =''
15
16 -- loop if data exists
17 while @ fetch_status = 0
18 begin
19 if (@ plist = '')
20 set @ plist = @ pm
21 else
22 set @ plist = @ plist + ',' + @ pm
23 fetch next from pm_cursor into @ pm
24
25 end
26
27 -- close the cursor
28 close pm_cursor
29 deallocate pm_cursor
30
31 -- return results
32 return @ plist
33
34
35 end
36
37