Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Постраничная выборка, убрать рекордсет встроенной процедуры (sql 2000)  [new]
dishlo
Member

Откуда:
Сообщений: 362
Процедура, алгоритм,
- дал: скл выражение, число строк на странице и текущую страницу.
- вернула: рекродсет, количество страниц и настоящую текущую страницу.

Встроенная процедура при открытии курсора выдает пустой рекордсет. В итого моя процедура возвращает 2 рекордсета.
Как можно избавиться от первого на стороне сервера? На клиенте все понятно.

--  exec dbo.GetPage 'select * from users', 14, 2
-- fetches page from rowset
create proc dbo.GetPage(
	  @sql varchar(8000) 
	, @rowsPerPage int = 10		-- rows on page
	, @currentPage int = 0 out	-- current page (in, out)
	, @numberOfPages int = 0 out) -- number of pages (out)
as

declare @cursor int, @rowcount int, @rownum int

-- Open
exec sp_cursoropen @cursor output, @sql, 1, 8193, @rowcount output



set @numberOfPages = @rowcount/@rowsPerPage + case when @rowcount%@rowsPerPage >0 then 1 else 0 end
if (@currentpage not between 1 and @numberOfPages )
	set @currentpage = 1

set @rownum = (@currentpage-1) * @rowsPerPage + 1

-- Fetch
exec sp_cursorfetch @cursor, 32, @rownum, @rowsPerPage

 

-- Close the cursor
exec sp_cursorclose @cursor

select @rowcount 'number of rows', @numberOfPages 'number of pages - out', @currentpage 'current page - out', @rownum 'row index'
--
11 дек 08, 12:41    [6555724]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная выборка, убрать рекордсет встроенной процедуры (sql 2000)  [new]
Шотов Вадим
Member

Откуда: Киев
Сообщений: 309
dishlo,

по ходу никак...

sp_cursoropen

Defines the attributes of an API server cursor, such as its scrolling behavior and the statement used to build the result set on which the cursor operates, then populates the cursor. The statement can contain embedded parameters.

Result Sets

Returns the result set generated by stmt, but containing no rows.
11 дек 08, 13:25    [6556114]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная выборка, убрать рекордсет встроенной процедуры (sql 2000)  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
INSERT ... EXEC?

-------------------------
There’s no silver bullet!
11 дек 08, 13:26    [6556128]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная выборка, убрать рекордсет встроенной процедуры (sql 2000)  [new]
dishlo
Member

Откуда:
Сообщений: 362
Sql динамический, а для insert exec надо таблицу заранее создать.
11 дек 08, 13:33    [6556183]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная выборка, убрать рекордсет встроенной процедуры (sql 2000)  [new]
Glory
Member

Откуда:
Сообщений: 104751
dishlo
Sql динамический, а для insert exec надо таблицу заранее создать.

Ну так разнесите создание курсора и его использование по разным процедурам
11 дек 08, 13:34    [6556201]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная выборка, убрать рекордсет встроенной процедуры (sql 2000)  [new]
dishlo
Member

Откуда:
Сообщений: 362
Спасибо, так и сделаю.
11 дек 08, 14:02    [6556421]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Постраничная выборка, убрать рекордсет встроенной процедуры (sql 2000)  [new]
лолл
Member

Откуда:
Сообщений: 450
Подниму трупак и выложу одно мое решение, к которому пришлось прибегнуть в одном сложном случае, может еще кому пригодится. Процедура возвращает страницу в одном наборе данных:

CREATE PROC dbo.p_PageGet
  @Statement    nvarchar(max),
  @PageNumber   int = 1,
  @PageSize     int = 20,
  @RowCount     int = null output,
  @RowsReturned int = null output
AS
BEGIN
  set nocount on

  declare
    @RowNumber int
    
  set @RowNumber = (@PageNumber - 1) * @PageSize + 1
  set @Statement = 
  N'declare @PagingCursor cursor 
declare PagingCursor cursor global scroll read_only for
' + @Statement + N'

set @PagingCursor = PagingCursor

open @PagingCursor

set @RowCount = @@cursor_rows

declare 
  @ColumnName sysname,
  @ColumnDataType sysname,
  @TableColumns nvarchar(max),
  @VarDeclaration nvarchar(max),
  @Variables nvarchar(max),
  @Script nvarchar(max)
  
declare Cols cursor local fast_forward read_only for
select 
  column_name,
  data_type = cast(case when data_type_sql in (175, 239, 231, 167, 165, 173) then st.[name] + ''('' + case when column_size > 8000 then ''max'' else cast(column_size as nvarchar(32)) end + N'')''
                        when data_type_sql in (106, 108) then st.name + ''('' + cast(column_precision as varchar) + '','' + cast(column_scale as varchar) + '')''
                        else st.name
                   end as nvarchar(100))
from master.dbo.syscursorrefs scr (nolock)
join master.dbo.syscursorcolumns scc (nolock) on scr.cursor_handl = scc.cursor_handle
join master.dbo.systypes st (nolock) on st.xusertype = scc.data_type_sql
where scr.cursor_scope = 2
      and scr.reference_name = ''PagingCursor''

open Cols

while 1 > 0
begin
  fetch next from Cols into @ColumnName, @ColumnDataType
  
  if @@fetch_status <> 0 break
  
  select 
    @TableColumns = case when @TableColumns is null then '''' else @TableColumns + '','' end + @ColumnName + '' '' + @ColumnDataType,
    @VarDeclaration = case when @VarDeclaration is null then '''' else @VarDeclaration + '','' end + ''@'' + @ColumnName + '' '' + @ColumnDataType,
    @Variables = case when @Variables is null then '''' else @Variables + '','' end + ''@'' + @ColumnName
end

set @Script = ''declare @Ret table (
'' + @TableColumns + '')

declare
'' + @VarDeclaration + ''

declare @i int

set @i = 1

fetch absolute @RowNumber from @PagingCursor into '' + @Variables + ''
if @@fetch_status = 0
  insert @Ret values ('' + @Variables + '')

while @@fetch_status = 0 and @i < @PageSize
begin
  fetch next from @PagingCursor into '' + @Variables + ''
  insert @Ret values ('' + @Variables + '')
  set @i = @i + 1
end

select * from @Ret

set @RowsReturned = @@rowcount''

exec sp_executesql
  @statement = @Script,
  @params = N''@PagingCursor cursor, @RowNumber int, @PageSize int, @RowsReturned int output'',
  @PagingCursor = @PagingCursor,
  @RowNumber = @RowNumber,
  @PageSize = @PageSize,
  @RowsReturned = @RowsReturned output
  
close PagingCursor
deallocate PagingCursor'

  exec sp_executesql
    @statement = @Statement,
    @params = N'@RowNumber int, @RowCount int output, @PageSize int, @RowsReturned int output',
    @RowNumber = @RowNumber,
    @RowCount = @RowCount output,
    @PageSize = @PageSize,
    @RowsReturned = @RowsReturned output
   
END
go
28 сен 12, 12:41    [13238062]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная выборка, убрать рекордсет встроенной процедуры (sql 2000)  [new]
лолл
Member

Откуда:
Сообщений: 450
да, пример использования:

declare @RowsReturned int, @RowCount int
exec p_PageGet 'select * from sys.objects', 1, 40, @RowCount out, @RowsReturned out
select @RowCount, @RowsReturned
28 сен 12, 12:43    [13238080]     Ответить | Цитировать Сообщить модератору
 Re: Постраничная выборка, убрать рекордсет встроенной процедуры (sql 2000)  [new]
лолл
Member

Откуда:
Сообщений: 450
нашел багу)))
исправленный вариант:

ALTER PROC dbo.p_PageGet
  @Statement    nvarchar(max),
  @PageNumber   int = 1,
  @PageSize     int = 20,
  @RowCount     int = null output,
  @RowsReturned int = null output
AS
BEGIN
  set nocount on

  declare
    @RowNumber int
    
  set @RowNumber = (@PageNumber - 1) * @PageSize + 1
  set @Statement = 
  N'declare @PagingCursor cursor 
declare PagingCursor cursor global scroll read_only for
' + @Statement + N'

set @PagingCursor = PagingCursor

open @PagingCursor

set @RowCount = @@cursor_rows

declare 
  @ColumnName sysname,
  @ColumnDataType sysname,
  @TableColumns nvarchar(max),
  @VarDeclaration nvarchar(max),
  @Variables nvarchar(max),
  @Script nvarchar(max)
  
declare Cols cursor local fast_forward read_only for
select 
  column_name,
  data_type = cast(case when data_type_sql in (175, 239, 231, 167, 165, 173) then st.[name] + ''('' + case when column_size > 8000 then ''max'' else cast(column_size as nvarchar(32)) end + N'')''
                        when data_type_sql in (106, 108) then st.name + ''('' + cast(column_precision as varchar) + '','' + cast(column_scale as varchar) + '')''
                        else st.name
                   end as nvarchar(100))
from master.dbo.syscursorrefs scr (nolock)
join master.dbo.syscursorcolumns scc (nolock) on scr.cursor_handl = scc.cursor_handle
join master.dbo.systypes st (nolock) on st.xusertype = scc.data_type_sql
where scr.cursor_scope = 2
      and scr.reference_name = ''PagingCursor''

open Cols

while 1 > 0
begin
  fetch next from Cols into @ColumnName, @ColumnDataType
  
  if @@fetch_status <> 0 break
  
  select 
    @TableColumns = case when @TableColumns is null then '''' else @TableColumns + '','' end + @ColumnName + '' '' + @ColumnDataType,
    @VarDeclaration = case when @VarDeclaration is null then '''' else @VarDeclaration + '','' end + ''@'' + @ColumnName + '' '' + @ColumnDataType,
    @Variables = case when @Variables is null then '''' else @Variables + '','' end + ''@'' + @ColumnName
end

set @Script = ''declare @Ret table (
'' + @TableColumns + '')

declare
'' + @VarDeclaration + ''

declare @i int

set @i = 1

fetch absolute @RowNumber from @PagingCursor into '' + @Variables + ''
if @@fetch_status = 0
  insert @Ret values ('' + @Variables + '')

while @@fetch_status = 0 and @i < @PageSize
begin
  fetch next from @PagingCursor into '' + @Variables + ''
  if @@fetch_status = 0
    insert @Ret values ('' + @Variables + '')
  set @i = @i + 1
end

select * from @Ret

set @RowsReturned = @@rowcount''

--print @Script
exec sp_executesql
  @statement = @Script,
  @params = N''@PagingCursor cursor, @RowNumber int, @PageSize int, @RowsReturned int output'',
  @PagingCursor = @PagingCursor,
  @RowNumber = @RowNumber,
  @PageSize = @PageSize,
  @RowsReturned = @RowsReturned output
  
close PagingCursor
deallocate PagingCursor'

  exec sp_executesql
    @statement = @Statement,
    @params = N'@RowNumber int, @RowCount int output, @PageSize int, @RowsReturned int output',
    @RowNumber = @RowNumber,
    @RowCount = @RowCount output,
    @PageSize = @PageSize,
    @RowsReturned = @RowsReturned output
   
END
go
28 сен 12, 13:53    [13238629]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить