高级存储过程！专家进！

[gordan19810801]

use CMZJ
go

create table tb_SZX
(
Mobile varchar(11) not null,
RowID int,
BranchID varchar(11) not null,
Arpu varchar(255),
JoinYears varchar(255),
City varchar(255),
Type varchar(255),
Sample varchar(255)
)
go

declare @mobile varchar(11),@branch varchar(12)
declare mobile_cursor cursor
for
select top 10 BranchID from SZX order by newid()
open mobile_cursor
fetch next from mobile_cursor into @mobile
while(@@fetch_status=0)
begin
declare branch_cursor cursor
for select top 40 * from
(select substring(Mobile,1,7) s from SZX group by substring(Mobile,1,7)) a
order by newid()
open branch_cursor
fetch next from branch_cursor into @branch
while (@@fetch_status=0)
begin
insert into tb_SZX select top 750 Mobile,RowID,BranchID,Arpu,JoinYears,City,Type,Sample from SZX where substring(Mobile,1,7)=@branch
fetch next from branch_cursor into @branch
end
close branch_cursor
deallocate branch_cursor
fetch next from mobile_cursor into @mobile
end
close mobile_cursor
deallocate mobile_curosr

insert into tb_SZX select top 750 Mobile,RowID,BranchID,Arpu,JoinYears,City,Type,Sample from SZX where substring(Mobile,1,7)=@branch order by newid()
go

以上的脚本为随机抽取40个mobile号段，10个省份，每个号段750条记录！

现在我想建立一个存储过程来实现和上面一样的功能，而不是像上面脚本一样直接新建一个表，再把记录插进去！请问这个存储过程该怎么建立呢？主要是三条SQL语句怎么连接起来？以下是我写的，但是不对！希望高手能指点一下！

--建立存储过程
if exists(select name from sysobjects where name='proc_SZX' and type='P')
drop procedure proc_SZX
go
create proc proc_SZX
@Mobile varchar(11),
@RowID int,
@BranchID varchar(11),
@Arpu varchar(255),
@JoinYears varchar(255),
@City varchar(255),
@Type varchar(255),
@Sample varchar(255)
output as
select top 10 BranchID from SZX order by newid()

select top 40 * from
(select substring(Mobile,1,7) mobile from SZX group by substring(Mobile,1,7)) a
order by newid()
select top 750 Mobile,RowID,BranchID,Arpu,JoinYears,City,Type,Sample from SZX order by newid()
go

declare @mobile varchar(11),@branch varchar(12)
declare mobile_cursor cursor
for
select top 10 BranchID from SZX order by newid()
open mobile_cursor
fetch next from mobile_cursor into @mobile
while(@@fetch_status=0)
begin
declare branch_cursor cursor
for select top 40 * from
(select substring(Mobile,1,7) mobile from SZX group by substring(Mobile,1,7)) a
order by newid()
open branch_cursor
fetch next from branch_cursor into @branch
while (@@fetch_status=0)
begin
insert into tb_SZX select top 750 Mobile,RowID,BranchID,Arpu,JoinYears,City,Type,Sample from SZX where substring(Mobile,1,7)=@branch
fetch next from branch_cursor into @branch
end
close branch_cursor
deallocate branch_cursor
fetch next from mobile_cursor into @mobile
end
close mobile_cursor
deallocate mobile_curosr

declare @Mobile varchar(11),@RowID int,@BranchID varchar(11),@Arpu varchar(255),@JoinYears varchar(255),@City varchar(255),@Type varchar(255),@Sample varchar(255)
execute proc_SZX @Mobile,@RowID,@BranchID,@Arpu,@JoinYears,@City,@Type,@Sample output

[caixia615]

看了头大,学习~

[gordan19810801]

呵呵！不用看下半部分~我就是想看看高手们是怎么来写实现上半部分功能的存储过程的！

[rookie_one]

来了，我就是传说中的专家~————————的马夫，
进来长长智利~