SQL分割字符串函数

创建函数:CREATE function [dbo].[SplitStr]( @SourceSql varchar(8000), @StrSeprate varchar(100)) returns @temp table(F1 varchar(100)) as begin declare @ch as varchar(100) set @SourceSql=@SourceSql+@StrSeprate while(@SourceSql<>'') begin set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1) insert @temp values(@ch) set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'') end return end

 

应用(存储过程)：

CREATE PROCEDURE [dbo].[UP_Student_GetListByStatus] @StatusList varchar(max)AS SELECT [Student].*FROM Student WHERE [Student].[PK_Status] IN (SELECT F1 FROM dbo.SplitStr(@StatusList,','))