回版主大人,我有一个分5段的分类编码(二进制:1111111 1111111 1111111 0000000 0000000 = 十进制34359721984),因为是分段的连续值,想要根据传过来的参数确定一个最小的缺失值,不知道我的思路对不对,类似这样:CREATE PROCEDURE [dbo].[mypcAddProCategory] ( @PcPID bigint, @PdName nvarchar(300), @A1 bigint, @A2 bigint, @BStep bigint ) ASset nocount on begindeclare @newPCID bigint set @newPCID = 0declare @indextable table([id] [bigint] IDENTITY(@A1+1,@BStep) NOT NULL, [nid] [bigint] NULL)insert into @indextable(nid) SELECT PcID FROM ProCategory WHERE (PcID > @A1) AND (PcID <= @A2) AND (PcPID = @PcPID) ORDER BY PcIDSELECT TOP 1 @newPCID = t.id FROM @indextable t WHERE t.id <> t.nid ORDER BY t.idprint @newPCIDend set nocount off
declare @indextable table([id] [bigint] IDENTITY('+CAST(@A1+1 as varchar)+','+CAST(@BStep as varchar)+' ) NOT NULL, [nid] [bigint] NULL)insert into @indextable(nid) SELECT PcID FROM ProCategory WHERE (PcID > '+CAST(@A1 as varchar)+ ') AND (PcID <= '+CAST(@A2 as varchar)+') AND (PcPID = '+CAST(@PcPID as varchar)+ ') ORDER BY PcID SELECT TOP 1 t.id FROM @indextable t WHERE t.id <> t.nid ORDER BY t.id '--select @sqlinsert into @t exec(@sql) declare @newPCID BIGINTselect @newPCID = newPCID from @t print @newPCID end set nocount off go
(
@PcPID bigint,
@PdName nvarchar(300),
@A1 bigint,
@A2 bigint,
@BStep bigint
)
ASset nocount on
begindeclare @newPCID bigint
set @newPCID = 0declare @indextable table([id] [bigint] IDENTITY(@A1+1,@BStep) NOT NULL, [nid] [bigint] NULL)insert into @indextable(nid) SELECT PcID FROM ProCategory WHERE (PcID > @A1) AND (PcID <= @A2) AND (PcPID = @PcPID) ORDER BY PcIDSELECT TOP 1 @newPCID = t.id
FROM @indextable t
WHERE t.id <> t.nid
ORDER BY t.idprint @newPCIDend
set nocount off
(
@PcPID bigint,
@PdName nvarchar(300),
@A1 bigint,
@A2 bigint,
@BStep bigint
)
ASset nocount on
begindeclare @sql varchar(8000)declare @t table(newPCID bigint)set @sql = '
declare @newPCID bigint
set @newPCID = 0
declare @indextable table([id] [bigint] IDENTITY('+CAST(@A1+1 as varchar)+','+CAST(@BStep as varchar)+'
) NOT NULL, [nid] [bigint] NULL)insert into @indextable(nid) SELECT PcID FROM ProCategory WHERE (PcID >
'+CAST(@A1 as varchar)+
') AND (PcID <= '+CAST(@A2 as varchar)+') AND (PcPID = '+CAST(@PcPID as varchar)+
') ORDER BY PcID SELECT TOP 1 t.id
FROM @indextable t
WHERE t.id <> t.nid
ORDER BY t.id
'--select @sqlinsert into @t
exec(@sql)
declare @newPCID BIGINTselect @newPCID = newPCID from @t
print @newPCID
end
set nocount off
go