Total RecordCount как OUTPUT выгружаемого набора результатов в хранимой процедуре

у меня вопрос по хранимым процедурам.

Я пытаюсь получить страницу набора результатов и количество записей всего набора.

Каждый из них работает сам по себе, но я не могу это объединить:

ALTER PROCEDURE dbo.pagingSCP
@PageStart INT, 
@PageSize INT, 
@RecordCount INT OUTPUT
AS
BEGIN
WITH AllRecords AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY MATNR) 
AS Row, viewStyleColorInModul.*
FROM viewStyleColorInModul WHERE SPRAS = 'D'
) SELECT * FROM AllRecords WHERE Row between 
@PageStart and @PageStart + @PageSize
END

(Возвращено 50 строк) @RecordCount = 0 @RETURN_VALUE = 0 Выполнение [dbo].[pagingSCP] завершено.

ALTER PROCEDURE dbo.pagingSCP
@PageStart INT, 
@PageSize INT, 
@RecordCount INT OUTPUT
AS
BEGIN
WITH AllRecords AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY MATNR) 
AS Row, viewStyleColorInModul.*
FROM viewStyleColorInModul WHERE SPRAS = 'D'
) SELECT @RecordCount = Count(*) From AllRecords
END

Строки не затронуты. (Возвращено 0 строк) @RecordCount = 43770 @RETURN_VALUE = 0 Завершено выполнение [dbo].[pagingSCP].

Можно ли как-то получить 50 строк и общее количество записей в одном запросе?

Заранее спасибо.


sql stored-procedures rowcount records
person csharpnoob    schedule 04.11.2010    source источник

Ответы (2)


arrow_upward
3
arrow_downward 

ALTER PROCEDURE dbo.pagingSCP
@PageStart INT, 
@PageSize INT, 
@RecordCount INT OUTPUT
AS
BEGIN
  -- get record count
  WITH AllRecords AS ( 
    SELECT viewStyleColorInModul.*
  FROM viewStyleColorInModul WHERE SPRAS = 'D'
  ) SELECT @RecordCount = Count(*) From AllRecords;

  -- now get the records
  WITH AllRecords AS ( 
   SELECT ROW_NUMBER() OVER (ORDER BY MATNR) 
   AS Row, viewStyleColorInModul.*
   FROM viewStyleColorInModul WHERE SPRAS = 'D'
  ) SELECT * FROM AllRecords 
  WHERE Row between @PageStart and @PageStart + @PageSize;
END

У вас есть два разных запроса, поэтому вы запускаете два разных SELECT и позволяете оптимизатору SQL оптимизировать каждый по отдельности. Даже если попытка получить оба запроса в одном SELECT возможна, это крайне контрпродуктивно и неоптимально.

В качестве примечания: в клиентском коде любой выходной параметр доступен только после повторения всех возвращаемых результатов.

person Remus Rusanu    schedule 04.11.2010
comment
это именно то, что мы думали, тоже. - person csharpnoob; 08.11.2010

arrow_upward
0
arrow_downward

Вот суть процедуры подкачки, которую мы использовали все время. Он работает, сначала выгружая все совпадающие записи во временную таблицу (ГДЕ SPRAS = 'D').

Затем он выбирает из временной таблицы только записи со страницы X страницы Y. Он также включает все записи исходного выбора (ГДЕ SPRAS = 'D').

ALTER PROCEDURE [dbo].[spSelectTempUsers] 

@Page       int = 0,
@NumPerPage int = 1

AS

SET NOCOUNT ON

CREATE TABLE #TempData 
(
[RowId]     [int] identity(1,1) ,
[UserId]    [int]               ,
[FirstName] [varchar](50)       ,
[LastName]  [varchar](50)       ,
[Email]     [varchar](255)      ,
[SPRAS]     [varchar](36)
)

INSERT INTO #TempData 
(
[UserId]    ,
[FirstName] ,
[LastName]  ,
[Email]     ,
[SPRAS]      
)

SELECT
[UserId]    ,
[FirstName] ,
[LastName]  ,
[Email]     ,
[SPRAS]      

FROM viewStyleColorInModul
WHERE [SPRAS] = 'D'


DECLARE @Count int
DECLARE @Pages int
DECLARE @i     int
DECLARE @j     int

IF @Page < 1 SET @Page = 1
SET @Count = (SELECT COUNT(RowId) FROM #TempData)
SET @Pages = @Count / @NumPerPage
IF (@Pages * @NumPerPage) < @Count SET @Pages = @Pages + 1
IF @Page > @Pages SET @Page = @Pages
SET @i = ((@Page -1) * @NumPerPage) +1
SET @j = @Page * @NumPerPage

SELECT 

ISNULL(t1.UserId,'')    as UserId,
ISNULL(t1.FirstName,'') as FirstName   ,
ISNULL(t1.LastName,'')  as LastName       ,
ISNULL(t1.Email,'')     as Email      ,
ISNULL(t1.SPRAS,'')     as SPRAS,

@Pages as Pages,
@Count as TotalRecords

FROM #TempData t1


WHERE   t1.RowId >= @i AND t1.RowId <= @j

ORDER BY t1.RowId

DROP TABLE #TempData
SET NOCOUNT OFF
person Michael Riley - AKA Gunny    schedule 06.11.2010