Sql存储过程拆分字符串
经常用到字符串拆分,C#里有函数直接调用,下面这个是SQL的:
1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 GO
4 -- =============================================
5 -- Author: Allen
6 -- Create date: 2008-6-11
7 -- Description: 批量导入客户
8 -- =============================================
9 Create PROCEDURE [dbo].[ImportCustomer]
10 @strTel varchar(8000),
11 @strName varchar(8000),
12 @strUserName varchar(50),
13 @groupID int
14 AS
15 BEGIN
16 declare @count int
17 declare @tempTel varchar(20)
18 declare @tempName varchar(20)
19 create table #temp(id int IDENTITY(1,1) NOT NULL primary key,tel varchar(20))
20 declare @strsql as varchar(8000)
21 select @strsql='' select @strsql='insert into #temp values ('''+replace(@strTel,',','''); insert into #temp values (''')+''')'
22 exec (@strsql)
23 create table #temp1(id int IDENTITY(1,1) NOT NULL primary key,[name] varchar(20))
24 declare @strsq2 as varchar(8000)
25 select @strsq2='' select @strsq2='insert into #temp1 values ('''+replace(@strName,',','''); insert into #temp1 values (''')+''')'
26 exec (@strsq2)
27 select @count = count(*) from #temp
28 while(@count>0)
29 BEGIN
30 select @tempTel = tel from #temp where id=@count
31 select @tempName = [Name] from #temp1 where id=@count
32 insert into dbo.SendCustomer(Tel,[Name],FirstDate,EndDate,UserName) values(@tempTel,@tempName,getdate(),getdate(),@strUserName)
33 insert into dbo.SendGroupCus(Tel,GroupID) values(@tempTel,@groupID)
34 Set @count = @count-1
35 END
36 drop table #temp
37 drop table #temp1
38 END
2 set QUOTED_IDENTIFIER ON
3 GO
4 -- =============================================
5 -- Author: Allen
6 -- Create date: 2008-6-11
7 -- Description: 批量导入客户
8 -- =============================================
9 Create PROCEDURE [dbo].[ImportCustomer]
10 @strTel varchar(8000),
11 @strName varchar(8000),
12 @strUserName varchar(50),
13 @groupID int
14 AS
15 BEGIN
16 declare @count int
17 declare @tempTel varchar(20)
18 declare @tempName varchar(20)
19 create table #temp(id int IDENTITY(1,1) NOT NULL primary key,tel varchar(20))
20 declare @strsql as varchar(8000)
21 select @strsql='' select @strsql='insert into #temp values ('''+replace(@strTel,',','''); insert into #temp values (''')+''')'
22 exec (@strsql)
23 create table #temp1(id int IDENTITY(1,1) NOT NULL primary key,[name] varchar(20))
24 declare @strsq2 as varchar(8000)
25 select @strsq2='' select @strsq2='insert into #temp1 values ('''+replace(@strName,',','''); insert into #temp1 values (''')+''')'
26 exec (@strsq2)
27 select @count = count(*) from #temp
28 while(@count>0)
29 BEGIN
30 select @tempTel = tel from #temp where id=@count
31 select @tempName = [Name] from #temp1 where id=@count
32 insert into dbo.SendCustomer(Tel,[Name],FirstDate,EndDate,UserName) values(@tempTel,@tempName,getdate(),getdate(),@strUserName)
33 insert into dbo.SendGroupCus(Tel,GroupID) values(@tempTel,@groupID)
34 Set @count = @count-1
35 END
36 drop table #temp
37 drop table #temp1
38 END