我没有写注释,看不懂得话问我。
CREATE PROCEDURE proc_visitor_sel_admincheck
@topcount int = 1000000,
@page int = 1,
AS
DECLARE @sSQL VARCHAR(8000)
DECLARE @counts INT
DECLARE @tempwords VARCHAR(100)
BEGIN TRAN
SET @sSQL = ''
If (@topcount IS NULL)
SET @topcount = 1000000
select @counts = Count(V_id) from Visitor
SET @sSQL = ''
If (@topcount IS NULL)
SET @topcount = 1000000
IF @counts <= @topcount
BEGIN
SET @sSQL = 'select top '+ CONVERT(VARCHAR(8), @counts) +' * from Visitor order by V_ID DESC'
END
Else
BEGIN
IF @page < (@counts/@topcount)/2
BEGIN
IF @page < 1 SET @page = 1
IF @page = 1
SET @sSQL = 'select top '+ CONVERT(VARCHAR(8), @topcount * @page) +' * from Visitor order by V_ID DESC'
ELSE
BEGIN
SET @sSQL = 'select top '+ CONVERT(VARCHAR(8),@topcount * @page) +' v_id from Visitor order by V_ID DESC'
SET @sSQL = 'select top '+ CONVERT(VARCHAR(8),@topcount) +' v_id from Visitor where v_id in ('+ @sSQL +') order by v_id ASC'
SET @sSQL = 'select top '+ CONVERT(VARCHAR(8),@topcount) +' * from Visitor where v_id in ('+ @sSQL +') order by v_id DESC'
END
END
ELSE
BEGIN
IF @page > (@counts/@topcount + 1) SET @page = @counts/@topcount + 1
SET @sSQL = 'select top '+ CONVERT(VARCHAR(8), @counts - @topcount * (@page-1)) +' v_id from Visitor order by V_ID ASC'
SET @sSQL = 'select top '+ CONVERT(VARCHAR(8),@topcount) +' * from Visitor where v_id in ('+ @sSQL +') order by v_id DESC'
END
END
--执行
EXECUTE(@sSQL)
--
IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
GO