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

Откуда:
Сообщений: 82
Здравствуйте, форумчане !
Есть некая View, возвращающая полный список абонентов сети.
Для сайта необходим постраничный просмотр этого списка. Соответственно нужна UDF, которая по заданному номеру страницы (листа) и кол-ву строк на листе будет возвращать подмножество записей. Доп. условие : предварительная сортировка по указанному столбцу.
Т.е. на входе три параметра: кол-во строк на листе, номер листа и имя столбца для сортировки, на выходе - от 0 до N записей, где N=кол-во строк на листе.

Помогите, пожалуйста, хотя бы ссылкой, где найти подходящий пример. Так давно не писал на TSQL, что почти все позабыл :)

Заранее спасибо за любую помощь
25 май 19, 16:23    [21893862]     Ответить | Цитировать Сообщить модератору
 Re: Функция для выборки записей в диапазоне номеров  [new]
vikkiv
Member

Откуда: London
Сообщений: 2624
MsGuns,

...ORDER BY Tel_Num DESC
OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY

например так https://rextester.com/live/YQDA46874
25 май 19, 17:29    [21893892]     Ответить | Цитировать Сообщить модератору
 Re: Функция для выборки записей в диапазоне номеров  [new]
MsGuns
Member

Откуда:
Сообщений: 82
Возвращает только один столбец с номерами строк, а нужно все столбцы вьюхи
25 май 19, 19:14    [21893914]     Ответить | Цитировать Сообщить модератору
 Re: Функция для выборки записей в диапазоне номеров  [new]
vikkiv
Member

Откуда: London
Сообщений: 2624
MsGuns,

вьюхи о которой здесь никто кроме автора не знает, а все хрустальные шары на лето расходятся по лесам в походы для обрядов и ритуалов.
убери строку WITH, заменяй объект после FROM на нужный и перечесляй после SELECT необходимые поля, строка с option тоже будет не нужна
25 май 19, 19:31    [21893921]     Ответить | Цитировать Сообщить модератору
 Re: Функция для выборки записей в диапазоне номеров  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
MsGuns
Соответственно нужна UDF


Либо у вас очень специфичные условия, вроде того , что надо учитывать обновление view при отображении списка пользователю или у вас версия сервера 11, и вы забыли об этом написать, либо вы не удосужились погуглить и/или с 2011 года не читали документацию.
25 май 19, 20:15    [21893934]     Ответить | Цитировать Сообщить модератору
 Re: Функция для выборки записей в диапазоне номеров  [new]
MsGuns
Member

Откуда:
Сообщений: 82
Версия сервера 11.0.5388.0

Нумерация в общем не нужна - я ее проставлю в разметке Html

DECLARE @colname1 nvarchar(max)
DECLARE @nlist int
DECLARE @qrowsbylist int

DECLARE @sql1 nvarchar(max)

-- Инициализация (отладка) --
Set @colname1 = 'FIO_Fam'
Set @nlist = 2
Set @qrowsbylist = 10

-- Формирование строки'
Set @sql1 = 'select * from UsFN_Get_FullTelInfo() order by '+@colname1

--exec sp_executesql @sql1 

set @sql1 = @sql1+' OFFSET '+ cast((@nlist-1)*@qrowsbylist as varchar) +' ROWS FETCH NEXT ' + cast(@qrowsbylist as varchar)+ ' ROWS ONLY'

exec sp_executesql @sql1 


Так все работает. Но если завернуть это в UDF, то кричит, что в теле функции вызов Execute невозможен
26 май 19, 17:09    [21894184]     Ответить | Цитировать Сообщить модератору
 Re: Функция для выборки записей в диапазоне номеров  [new]
vikkiv
Member

Откуда: London
Сообщений: 2624
MsGuns,

Не execute, а конкретней: в функции запрещён вызов хранимых процедур (за исключением узкого круга избранных M$)
почему-то Microsoft из-за вопросов безопасности (а Dyn_SQL один из самых опасных подходов) не отдаёт это даже на усмотрение администраторов сервера
я обходил через Assemblies (делал свою C# сборку и встраивал в SQL Сервер) - тогда вызов функции (в свою очередь вызывающей SP) из сборки работает.

Если есть возможность то обычно вместо UDF это заворачивают в stored_procedure (с теми-же параметрами), тогда такого ограничения нет (там другую SP можно вызывать)

Но вообще многое из этого делается на клиенте при формировании нормального запроса (из выбранных пользователем параметров)
26 май 19, 17:49    [21894196]     Ответить | Цитировать Сообщить модератору
 Re: Функция для выборки записей в диапазоне номеров  [new]
fkthat
Member

Откуда:
Сообщений: 1921
Никакой sql_execute не нужен (имхо, это вообще крайняя мера).

Вариант без нумерации:
create function udf_GetPagedView(
  @colname sysname, 
  @offset int,
  @length int
) returns table as 
return select * from sys.objects
 order by case
  when @colname = 'object_id' then object_id
  when @colname = 'name' then name
  -- тут еще другие могут быть столбцы
 end
 offset @offset rows fetch next @length rows only
go

И с нумерацией:
create function udf_GetPagedViewWithNum(
  @colname sysname, 
  @offset int,
  @length int
) returns table as 
return
 select * from
 (select row_number() over(
    order by case
      when @colname = 'object_id' then object_id
      when @colname = 'name' then name
      -- тут тоже могут быть другие столбцы
    end) RowNum, * from sys.objects) t
  where @offset < RowNum and RowNum <= @offset + @length
go
26 май 19, 18:29    [21894211]     Ответить | Цитировать Сообщить модератору
 Re: Функция для выборки записей в диапазоне номеров  [new]
MsGuns
Member

Откуда:
Сообщений: 82
fkthat, все получилось ОТЛИЧНО ! Второй вариант (с нумерацией) не прошел ("Функция Row_Number не имеет аргументов"), но вполне хватит первого ! Огромного спасибо !

Отдельные благодарности:
vikkiv - за полезные пояснения и быстроту реакции
PizzaPizza - за розги - без них хуже доходит :)
fkthat - за фактически готовое решение и науку
всем - за внимание и потраченное время

Отличный форум !
27 май 19, 12:46    [21894687]     Ответить | Цитировать Сообщить модератору
 Re: Функция для выборки записей в диапазоне номеров  [new]
MsGuns
Member

Откуда:
Сообщений: 82
vikkiv
Но вообще многое из этого делается на клиенте при формировании нормального запроса (из выбранных пользователем параметров)


У меня MVC + EDM, подход: максимальный вынос логики на SQL-сервер.
27 май 19, 12:52    [21894690]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить