JAVA高级面试进阶训练营视频教程

admin

--添加表值函数
 1 ALTER function [dbo].[func_splitstring]
 2 (@str nvarchar(max),@split varchar(10))
 3 returns @t Table (id varchar(100))
 4 as
 5 begin
 6     declare @i int
 7     declare @s int
 8     set @i=1
 9     set @s=1
10     while(@i>0)
11     begin    
12         set @i=charindex(@split,@str,@s)
13         if(@i>0)
14         begin
15             insert @t(id) values(substring(@str,@s,@i-@s))
16         end   
17         else begin
18             insert @t(id) values(substring(@str,@s,len(@str)-@s+1))
19         end
20         set @s = @i + 1   
21     end
22     return
23 end

ALTER PROCEDURE [dbo].[GetStudentListByStudentId]
@StudentId nvarchar(500)
as
BEGIN
--调用表值函数转换类型
select * from Student where StudentId in(SELECT cast(id as int) StudentId FROM dbo.func_splitstring(@StudentId,','))
end