User-Defined Function usage

Application 1:

1. -- Use Lyds # DB
2. -- Select ID, count (TEST) from t_test group by id -- this is the effect of grouping.
3. -- Data table
4. ID (INT) test (nvarchar (50 ))
5. 1 aaa
6. 2 bbb
7. 1 CCC
8. 2 ddd
9. -- Create a UDF
10. Create Function DBO. f_str (@ id int)
11. Returns varchar (8000)
12. As
13. Begin
14. Declare @ r varchar (8000)
15. Set @ r =''
16. Select @ r = @ r + ',' + Test
17. From t_test
18. Where id = @ ID
19. Return stuff (@ r, 1, 1, '') -- remove the first comma
20. End
22. -- Call a custom function
23. Select ID, test = DBO. f_str (ID)
24. From t_test
25. Group by ID
27. -- Output content
28. Id Test
29. 1 AAA, CCC
30. 2 BBB, DDD

1. The first custom function:
2. Create Function DBO. f_first
3. (
4. @ ID int,
5. @ Name nvarchar (20)
6. )
7. Returns varchar (8000)
8. As
9. Begin
10. Declare @ r varchar (8000)
11. Set @ r = @ name + @ ID -- Here + converts the numeric type to a number by default.
12. Return @ r
13. End
14. Usage:
15. Select DBO. f_first (2, '2') as returnstr

End