求一个自定义排序的存储过程

怎么没人帮忙啊。

TO:gc_ding(E.T)

因为3号跟5号中间断号了。所以此时5号，8号不能加1。

简单的触发器就行了!

两种思路：
１、变量循环 定义一个变量，作为循环计数器，从１开始循环，每次都将第一个在表格中不存在的值填充到表格且跳出循环；如果是一次填充多个，就在多个都填充完后才跳出循环；
２、如果填充的是一个物理临时表，为提高效率，可以建一个只有序列字段的物理表并为它填充尽量多的值后建立升序索引，每次需要定义记录序列号时，直接执行查询：select min(临时id) from 临时表 where not exists (select 1 from 目标表 where 填充序列字段 = 临时表.临时id) 来获取最新的可以填充的序列号

CREATE TRIGGER tg_insertdata ON [dbo].[num]
instead of INSERT
AS
begin
if exists(select id from num where id = (select id from inserted))
begin
-------------------如果存在此记录的话
declare @middle varchar(20)
select @middle=id from inserted
declare @table table (cid int)
insert into @table select id from num where id=@middle
set @middle=@middle+1
while @@rowcount > 0
begin
if exists(select id from num where id=@middle)
begin
insert into @table select @middle
set @middle=@middle+1
end
else
begin
break
end
end
update num set id=id+1 where id in (select cid from @table)
insert into num select id from inserted
end
else
begin
insert into num select id from inserted
end
end

我来写一个存储过程

create table ta(id int)
insert ta
select 1
union all select 2
union all select 3
union all select 5
union all select 8

用触发器:
create trigger test_tr on ta
instead of insert
as
begin
DECLARE @i int
DECLARE roy CURSOR
FOR SELECT * from inserted
OPEN roy
FETCH next FROM roy into @i
WHILE @@FETCH_STATUS = 0
begin
if exists(select 1 from ta where id=@i)
begin
update a
set id=id+1
from ta a
where (exists(select 1 from ta where a.id=id+1)
or exists(select 1 from ta where a.id=id-1) )
and id!<@i
end
insert ta select @i
FETCH NEXT FROM roy INTO @i
end
CLOSE roy
DEALLOCATE roy
end

--测试
insert ta select 1 union all select 6

查询:

select * from ta order by id
id
-----------
1
2
3
4
5
6
8

（所影响的行数为 7 行）

楼上帅哥提得好,由于楼主的列子也没有考虑到这种情况。偶发完贴完也考虑到这个问题，不能连发三贴。
以下贴出正确答案。
create table ta(id int)
insert ta
select 1
union all select 2
union all select 3
union all select 6
union all select 7
union all select 12
union all select 13

用存储过程:
create proc test_p @id int
as
begin
declare @j int,@sql varchar(1000)
if exists(select 1 from ta where id=@id)
begin
select @j=@id,@sql=''
while exists(select 1 from ta where id=@j)
begin
select @sql=@sql+','+rtrim(id) from ta where id=@j
select @j=@j+1
end
set @sql=stuff(@sql,1,1,'')
exec('update ta set id=id+1 where id in ('+@sql+')')
insert ta select @id
end
else
insert ta select @id
end

测试:
exec test_p 1
exec test_p 8
--truncate table ta 清空表数据

用触发器:
create trigger test_tr on ta
instead of insert
as
begin
declare @id int,@j int,@sql varchar(1000)
declare roy cursor
for select id from inserted
open roy
fetch next from roy into @id
while @@fetch_status=0
begin
if exists(select 1 from ta where id=@id)
begin
select @j=@id,@sql=''
while exists(select 1 from ta where id=@j)
begin
select @sql=@sql+','+rtrim(id) from ta where id=@j
select @j=@j+1
end
set @sql=stuff(@sql,1,1,'')
exec('update ta set id=id+1 where id in ('+@sql+')')
end
insert ta select @id
fetch next from roy into @id
end
CLOSE roy
DEALLOCATE roy
end

测试:
insert ta
select 1
union all select 2
union all select 3

删除测试:
drop proc test_p
drop table ta

其他字段你没写出来，人家没法写啊，当然只能给个大概思路了

自己稍微改造一下吧

以上的create proc test_p (@id int--把'('去掉
如果是触发器
insert ta select @id
改为
insert ta(id) select @id --除id列外,其它列为null

create proc test_p (@id int
as
begin
update ta set id=ta.id+1
where id>=@id and id<=(select min(a.id+1) min_id FROM ta a
left join ta b on a.id=b.id-1
where a.id>=@id

and b.id is null)

insert into ta(id,...)
select @id,...
end

xiequanqin(XQQ) 大哥，这样修改了一下，每次插入一条记录，所以大于@pid的数都加上了1，我希望如果有断号，后面就停止加1，下面是我修改后的代码，仍然没有达到我期望的要求。再请指教。roy_88大哥的代码不大看懂~~惭愧~~
alter proc test_p
(
@pid int,
@standard char(20),
@name_zh nvarchar(20),
@name_en char(50),
@img_path char(50)
)
as
begin
update product set pid=product.pid+1
where pid>=@pid and pid<=(select min(a.pid+1) min_id FROM product a
left join product b on a.pid=b.pid-1
where a.pid>=@pid

and b.pid is null)

insert into product(pid,standard,name_zh,name_en,img_path)
select @pid,@standard,@name_zh,@name_en,@img_path
end

xiequanqin(XQQ) 大哥，这样修改了一下，每次插入一条记录，所以大于@pid的数都加上了1，我希望如果有断号，后面就停止加1，下面是我修改后的代码，仍然没有达到我期望的要求。再请指教。roy_88大哥的代码不大看懂~~惭愧~~
alter proc test_p
(
@pid int,
@standard char(20),
@name_zh nvarchar(20),
@name_en char(50),
@img_path char(50)
)
as
begin
update product set pid=product.pid+1
where pid>=@pid and pid<=(select min(a.pid+1) min_id FROM product a
left join product b on a.pid=b.pid-1
where a.pid>=@pid

and b.pid is null)

insert into product(pid,standard,name_zh,name_en,img_path)
select @pid,@standard,@name_zh,@name_en,@img_path
end

问题已解决，马上结帖，各位朋友能留下MSN或QQ吗。

可以交个朋友
以后多多交流

这个 用户名+Hotmail邮箱~