first sql store prucedure会者不难，难者不会！！！
declare @i int
declare @myclmn char(100)
declare @sql char(100)
declare mycursor CURSOR
for
select max(maxlist) from sc39
open mycursor
declare @maxline sysname
FETCH NEXT FROM mycursor into @maxline
print @maxline
set @i=0
while @i<@maxline
begin
set @i=@i+1
set @myclmn='price'+cast(@i as char(20))
print @myclmn
set @sql='ALTER TABLE sc_39 ADD '+rtrim(@myclmn)+' int default 0'
print @sql
exec (@sql)
end
CLOSE mycursor
DEALLOCATE mycursor
set @i=0
declare @pn sysname,@maxlst sysname,@price sysname,@sql2 char(100),@k int,@clmn char(100)
declare mycursor2 CURSOR
for
select SC39001,maxlist from sc39
open mycursor2
FETCH NEXT FROM mycursor2 into @pn,@maxlst
WHILE (@@FETCH_STATUS <> -1)
begin
set @k=0
print @pn
set @i=-1
while @i<4
begin
set @i=@i+1
print 'i:'+cast(@i as char(10))
declare mycursor3 CURSOR
for select SC39004 from SC390100 where SC39001=@pn and SC39002=@i
open mycursor3
FETCH NEXT FROM mycursor3 into @price
WHILE (@@FETCH_STATUS <> -1)
begin
print 'Price:'+cast(@price as char(100))
--print 'sdfdsf'
set @k=@k+1
print 'k:'+cast(@k as char(100))
if @i=0
begin
set @clmn='price1'
end
else
set @clmn='price'+cast(@i as char(10))
if @k=1
begin
set @sql2='insert into sc_39(SC39001,'+rtrim(@clmn)+') values ('+rtrim(@pn)+','+@price+')'
end
else
set @sql2='update sc_39 set '+rtrim(@clmn)+'='+@price+' where SC39001='+rtrim(@pn)
print @sql2
exec (@sql2)
FETCH NEXT FROM mycursor3 into @price
end
CLOSE mycursor3
DEALLOCATE mycursor3
end
FETCH NEXT FROM mycursor2 into @pn,@maxlst
end
CLOSE mycursor2
DEALLOCATE mycursor2
下面就是整合成storeprocedure.
