Форум программистов, компьютерный форум, киберфорум
Наши страницы
Базы данных
Войти
Регистрация
Восстановить пароль
 
 
Рейтинг 4.96/23: Рейтинг темы: голосов - 23, средняя оценка - 4.96
Maksim
1

Подскажите, как сделать хранимую процедуру в MS SQL для постраничного вывода?

13.08.2007, 14:37. Просмотров 4188. Ответов 30
Метки нет (Все метки)

Есть следующие мысли:
Если можно сделать запрос, который добавит еще одно поле, проиндексированное от 0 до количества записей, я смогу решить задачу...
Возможно ли сделать такой запрос?
А может есть другие способы решения?
В Oracle вроде можно получить номер строки в запросе... А в MS SQL?
Similar
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
13.08.2007, 14:37
Ответы с готовыми решениями:

Подскажите как сделать скрипт на php для My sql
меня такая проблема, нужен скрипт на пхп который бы показывал все записи в...

Как создать хранимую процедуру в IDS Informix
как создать хранимую процедуру в IDS Informix?

Передача рисунка в хранимую процедуру
Есть таблица (SQL 200) CREATE TABLE . ( IDENTITY PRIMARY KEY NOT NULL,...

Передача данных из клиента VB 6.0 в хранимую процедуру MS SQL Server 7.0 (+++)
В проге VB есть переменная Price as Currency Надо в хронимую процедуру на MS...

Не могу использовать в MS SQL Server хранимую процедуру, где запрос длиннее 4000 символов
Объявляю строку запроса как nvarchar(4000)... Может есть какой способ обойти...

30
big_mammoth
0 / 0 / 0
Регистрация: 25.07.2007
Сообщений: 39
13.08.2007, 17:11 2
Вообщем нету у MS SQL такого понятия как Row ID
(Хотя могу ошибаться - но в bookonline не нашел) -
единственное что можно попробовать - добавить в таблицу колонку со свойством IDENTITY и использовать её

Прии этом для того чтобы ограничить количечтов выводимых строк в странице достаточно
выполнить

set rowcount @value
0
Maksim
13.08.2007, 17:53 3
Во первых, я делаю запрос не из одной таблицы, а из многих, делая довольно сложные объединения и Views...
Нужен именно такой селект, а не таблица... Была у меня идея создавать временную таблицу с автоинкрементным полем, и заполнять ее данным запросом, а потом делать запрос из нее... Но я слабо представляю, как это делается, и как это будет себя вести при доступе многочисленных пользователей одновременно... Да и слишком тяжело и грубо получается...

set Rowcount... Отпадает однозначно... Как листать страницы? Как перейти к странице номер такой-то? Это можно сделать имея вышеописаный пронумерованный набор записей...
Делать селект
WHERE ID > ((PageNomer-1)*PageSize) AND ID < (PageNomer*PageSize) ...

Help! Я очень тупорыл в этом вопросе...
AndreP
0 / 0 / 1
Регистрация: 22.07.2007
Сообщений: 260
13.08.2007, 18:01 4
Микрософт советует извращаться следующим способом.
Пусть например надо считать строки с 101 по 150 из вьюхи View1. Тогда записываем первые 150 строк во временную таблицу

SELECT TOP 150 into #temptable from View1 order by ...

Грохаем первые 100 записей

SET ROWCOUNT 100
DELETE #temptable

(тут в принципе можно грохнуть и нужные записи, так как у DELETE нет ORDER BY :-)

Возвращаем на место ROWCOUNT и считываем нужные данные

SET ROWCOUNT 0
SELECT * from #temptable order by ...
0
Maksim
13.08.2007, 21:01 5
Идея неплохая... Я только совсем не знаю, как работать с этими временными таблицами... Нельзя разве создать точно так же таблицу с автоматически проиндексированным полем? И делать описаный мной выше SELECT? Чтобы не убить нужные записи, поскольку DELETE не понимает ORDER BY...? Но кстати, INSERT то понимает? Тогда, в принципе проблемы нет... Еще одна идея созрела... Хочу например получить записи с 200 по 200 + PageSize... Устанавливаю в процедуре set rowcount @page_size... Делаю SELECT TOP 200+PageSize... А потом нужно как-то перевернуть мой набор записей... как, не знаю... Может опять временная таблица? И еще, желательно чтобы процедура возвращала общее число записей... Входные параметры определены, два из них номер страницы и ее размер... Надо еще и проверить, что страница не за пределами селекта...
AndreP
0 / 0 / 1
Регистрация: 22.07.2007
Сообщений: 260
14.08.2007, 12:11 6
Максим, ты правильно мыслишь, непонятно почему еще возникают вопросы, работа с временными таблицами мало чем отличается от работы с постоянными, а DELETE с вероятностью 99.9% убъет именно первые записи (это и надо в данном случае). Истинное количество записей, вставленных в таблицу, вернет переменная @@ROWCOUNT, если ее вызвать сразу после вставки.
0
Maksim
14.08.2007, 13:07 7
Da ya prosto nikogda s etim ne rabotal v jizni...
Poetomu i slojnosti...
Poetomu i sprashivayu....

Gromadnoe spasibo za help!!!
Maksim
15.08.2007, 00:21 8
Короче, получилось у меня следующее:
Имею мощный и навороченный селект, заполняю этим селектом временную таблицу @temp_source...
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Определяю переменные --
DECLARE @page_size INT
DECLARE @page_number INT
DECLARE @rowmin INT 
DECLARE @rowmax INT 
 
-- Определяю временную таблицу с одним полем, для примера --
DECLARE @temp_table TABLE (name VARCHAR(50))
 
-- Здесь временное присваивание (для тестов в Query Analizer, можете протестировать)-- 
SET @page_size = 10
SET @page_number = 1
 
-- и вычисление начала и конца страницы --
SET @rowmax = @page_number*@page_size
SET @rowmin = (@page_number-1)*@page_size
 
-- Здесь селекты и удаления, настроено для Query Analizer --
 
-- Создание временной таблицы --
SET ROWCOUNT @rowmax
INSERT INTO @temp_table SELECT name FROM @temp_source ORDER BY name
 
-- Удаление лишнего --
IF @rowmin > 0 BEGIN
SET ROWCOUNT @rowmin
DELETE FROM @temp_table 
END
 
-- Окончательная выборка ---
SET ROWCOUNT 0
SELECT * FROM @temp_table
 
-- Cброс таблиц --
DELETE @temp_table
DELETE @temp_source
Все работает замечательно... При указании номера страницы '0', возвращает все целиком,
при указании несуществующего номера возвращает пустую выборку, корректно возвращает последнюю страницу...
Спасибо всем...
Еще одно... Желательно чтобы процедура возвращала такие параметры как число записей в листаемом селекте, ну и можно еще и номер страницы и ее размер до кучи. Которые она получила в виде параметров...
Делаю SELECT COUNT(*) .... , с теми же самыми условиями селекта... Дальше? Как это получить из вызова процедуры? Добавлять еще одну колонку в запрос? Как в нее засунуть этот 'count'?
Подскажите... Я пока в этом полный чайник... Когда поумную, тоже чего-нибудь подскажу...
И еще... Надо ли очищать память после использования временных таблиц? И как?
Maksim
15.08.2007, 00:30 9
Опять заморочка... Листаю я значит свои результаты поиска, допустим открыв страницу в браузере... А в это время кто-то изменил базу данных... Я уже не получу верные результаты пролистывания... Получу, но на какой-то из страниц могут появиться записи, которых я еще не видел...
В общем, нарушен будет вывод... Страшного ничего, но желательно ввести какой-то флаг, который будет возвращаться процедурой, и проверять, не было ли изменений таблицы со времени последнего запроса очередной страницы. Если были, то хотя бы предупреждаем пользователя о том, что, мол, какая-то из используемых в селекте таблиц была изменена, желательно просмотреть все заново, могли появиться новые данные... Как получить и как передать подобное уведомление? Вообще, результатом хранимой процедуры является recordset, или что? Можно ли передавать еще какие-то параметры? Сообщения об ошибках, etc...? Как?
Доступ к процедуре произвожу с ASP страницы следующим образом:
Visual Basic
1
2
3
4
5
6
7
8
9
Set cmd=Server.CreateObject('ADODB.Command')
Set rs=Server.createObject('ADODB.Recordset')
cmd.ActiveConnection='provider=SQLOLEDB.1;Persist Security Info=False;User ID=MyID;Initial Catalog=MyCatalog;Data Source=MyServer' 
cmd.commandtype= 1
cmd.commandtext 'name_of_stored_procedure 'Параметр1','Параметр2','...','...','Параметр50''
rs.CursorLocation=3
rs.CursorType=3
rs.LockType=4
rs.open cmd
Все ли правильно? Это все работает, но хотелось бы снять сомнения... Заказчик привередливый...
AiK
15.08.2007, 01:09 10
1)
Число записей можно вернуть через параметр SP.
Делается так:
SQL
1
2
3
4
5
6
7
8
9
CREATE proc my_proc
@rowcount INT OUT
AS
...
 
DECLARE @rowcount INT
EXEC my_proc @rowcount = @rowcount OUT
 
SELECT @rowcount
2) правилом хорошего тона считается удаление временной таблицы (drop table #yourtable)

3) вообще результатом работы процедуры является
а) ничего (просто какие-то изменения в БД происходят)
б) резалтсет (рекордсеты только в VB бывают ), которых может быть сколько угодно. Но как правило, в силу ограниченности клиентов (а иногда драйверов) работают с одним.
в) return value
процедуре возвращается как return @intvalue
на клиенте или в другой процедуре обрабатывается как:
SQL
1
2
DECLARE @rc INT
EXEC @rc  = my_proc @param=@VALUE
г) возвращаемый параметр (см. 1.)
д) сообщение об ошибке
вызывает либо сервер сам, либо разработчик при помощи raiserror

4) Для того, чтобы заведомо получать свежие данные велосипедов со временными таблицами не изобретают. Просто _на клиенте_ выфетчивают только нужные записи на каждой странице.
Твой вариант с флагами плох тем, что если появилась новая запись в таблице, то ты не знаешь на какой из страниц она появится - так как в хороших системах пользователю предлагают самому выбрать число возвращаемых записей на страницу.

С уважением,
Артём.
Deo
15.08.2007, 02:28 11
1. Временные таблицы в MS SQL два типа глобальные и локальные. Глобальные существуют до тех пор пока их не удалили или после рестарта MSSQL, доступны всем пользователям. Локальные видны только тому кто их создал, уничтожаются после диссконекта пользователя или их специально удалить. Если их использовать в хранимой процедуре, то после окончания работы они уничтожаются. Но ничто тебе не мешает вернуть RECORDSET из временной таблицы, которая создана внутри процедуры. Если использовать FETCH, даже на стороне клиента, (как предлогает Артем) то сервер может заблокировать всю таблицу, если в ней записей не велико. В ASP для RECORDSET имеется свойство PAGESIZE используй его. Получая данные при помощи хранимой процедуры или еще как-то ты получаешь снимок данных, и пользователь не видет что происходит с записями в самой базе. Чтобы получить изменения нужно заново выполнить, в твоем случае, процедуру. Это почти везде так. Пример данного форума, отослав свой ответ, ты его не видешь, до тех пор пока не обновил окно.
Maksim
15.08.2007, 15:14 12
Ya imeyu v vidu, esli ya poluchayu nabor zapisey iz hranomoi procedury, mogu ya odnovremenno peredavat' esche kakie-nibud' znacheniya?
Te je soobscheniya, chislo stranic, kolichestvo zapisei, etc... Kak?

Recordset.... PageSize....
Problem to net, PageAbsolute, PageSize, bol'she nichego...
Dlya etogo nado ves' recordset taschit' na mesto ego obrabotki, na Web Server v moem sluchae...
A ya kak raz i hochu etogo izbejat'....
RecordSet mojet byt' gromadnym...
AndreP
0 / 0 / 1
Регистрация: 22.07.2007
Сообщений: 260
15.08.2007, 18:15 13
Если из процедуры возвращаете записи то с возвращаемыми параметрами могут быть проблемы, поэтому проще всего вернуть еще что то это вместо

SELECT * FROM @temp_table

написать

SELECT *, @par1 as par1, @par2 as par2 FROM @temp_table
0
big_mammoth
0 / 0 / 0
Регистрация: 25.07.2007
Сообщений: 39
15.08.2007, 18:51 14
Ничего подобного - проблем с получением оттпутных параметров нет, Нужно учитывать что их можно получить только после того как считан весь рекордсет или все рекордсеты , после этого можно считывать параметры
0
AndreP
0 / 0 / 1
Регистрация: 22.07.2007
Сообщений: 260
15.08.2007, 19:35 15
To Mammoth
Что в коде VB означает 'считан весь
рекордсет'
0
big_mammoth
0 / 0 / 0
Регистрация: 25.07.2007
Сообщений: 39
15.08.2007, 20:57 16
Считан в смысле - recordset.NextRecordset возвращает Nothing
т.е считаны уже все данные и буфер пуст - обнаружено методом научного тыка

На анологичную ситуацию я наткнулся в DB-Library
там пока не очистишь буфер данных, невозможно получить не
возращаемые параметры, ни код возврата хранимой процедуры. Т.к. OLE DB Provider for MS SQL и DB-Library работают с использованием TDS(Tabular Data Stream) , то ситуации получаются схожие, пока все данные не считаны - параметры не получить

вот пример:
Выполняется хранимая которая возвращает один рекордсет
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Set aCommand = New ADODB.Command
 
 With aCommand
  .Name = 'Query1'
  .CommandType = adCmdStoredProc
  .CommandText = 'dbo.tar_CountryGetList'
  .Parameters.Append .CreateParameter('return_code', adInteger,                                                                                                                                 adParamReturnValue, 4)
 
  ' Если есть возращаемые параметры то их сдесь нужно   '   ' добавить.Для них adParamReturnValue меняется 
  ' на adParamOutput
 
  Set .ActiveConnection = aConnection
 End With
 
 Set aRecordSet = aCommand.Execute()
 
'
'  Доходим до конца рекордсета
' 
 
 While Not aRecordSet.EOF
 
    ' Здесь полученных обработка данных данных 
 
    aRecordSet.MoveNext
 
 Wend
  
 '
 ' Запрашиваем следующий набор данных
 ' если хранимая возращает только один рекордсет 
 ' то после можно получить код возврата и параметры
 '
  aRecordSet.NextRecordset  
 
  Debug.Print CInt(aCommand.Parameters('return_code').Value)
0
Maksim
15.08.2007, 21:43 17
SQL
1
SELECT *, @par1 AS par1, @par2 AS par2 FROM @temp_table
Ya imenno tak i sdelal, prosto mne eto kajetsya neskolko ubogo...
Parametr odin, a ego prihoditsya v kajdoy stroke peredavat'....

Ili eto vpolne normal'no?
AndreP
0 / 0 / 1
Регистрация: 22.07.2007
Сообщений: 260
16.08.2007, 11:34 18
Конечно это избыточно, но иначе чтобы получить параметры придется прокрутить возвращаемые записи до конца (как предлагает Mammoth). Например, пусть мы имеем хранимую процедуру с возвращаемыми параметрами и выборкой из временной таблицы
SQL
1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE my_proc 
@par1 INT output,  
@par2 VARCHAR(32) output 
AS 
SET NOCOUNT ON
CREATE TABLE #tmp (t VARCHAR(32))
INSERT INTO #tmp SELECT * FROM qqq
SET @par1=10
SET @par2='test процедура'
SELECT * FROM #tmp
SET NOCOUNT OFF
GO
Тогда чтобы считать RecordSet и параметры в VB придется сделать следующее
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
  Dim cnn As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim prm1 As ADODB.Parameter
  Dim prm2 As ADODB.Parameter
  Dim rs As ADODB.Recordset
  Dim strCnn As String
 
  ' создание объектов и подключение к БД
  Set cnn = New ADODB.Connection
  strCnn = 'Provider=sqloledb;' & _
  'Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; '
  cnn.Open strCnn
  Set cmd = New ADODB.Command
  Set cmd.ActiveConnection = cnn
  cmd.CommandText = 'my_proc'
  cmd.CommandType = adCmdStoredProc
  cmd.CommandTimeout = 15
  ' создаем параметры
  Set prm1 = New ADODB.Parameter
  prm1.Name = 'par1'
  prm1.Type = adInteger
  prm1.Size = 4
  prm1.Direction = adParamOutput
  Set prm2 = New ADODB.Parameter
  prm2.Name = 'par2'
  prm2.Type = adVarChar
  prm2.Size = 32
  prm2.Direction = adParamOutput
  ' прикрепляем параметры
  cmd.Parameters.Append prm1
  cmd.Parameters.Append prm2
  ' создаем RecordSet вызывая хранимую процедуру
  Set rs = cmd.Execute()
  
  ' ... здесь надо обработать возвращаемые записи
  
  ' прокручиваем записи до конца
  While Not (rs Is Nothing Or rs.State = adStateClosed)
    While Not rs.EOF
      rs.MoveNext
    Wend
    rs.NextRecordset
  Wend
  ' теперь можно считать параметры
  MsgBox prm1.Value & ' ' & prm2.Value
0
Maksim
16.08.2007, 12:21 19
Slushai, AndreP...
A esli sdelat' prosto

Select 1.....
UNION
Select 2....

V pervom stroka s parametrami, vo vtorom - dannye...

Eto normal'no? Skaji svoe mnenie... Ti, pohoje, gramotnyi programmer...
AndreP
0 / 0 / 1
Регистрация: 22.07.2007
Сообщений: 260
16.08.2007, 13:43 20
Правильно мыслишь. Можно и так

Select 1.....
UNION
Select 2....

только придется
1. обеспечить равенство количества столбцов и их типов в первом и во втором SELECT (впрочем во втором, где параметры, ненужные столбцы можно заменить на NULL)
2. отыскать строку с параметрами в результате (порядок возврата не регламентирован, скорее всего второй SELECT вернется первым)
0
16.08.2007, 13:43
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
16.08.2007, 13:43

Создать хранимую процедуру для вычисляемого поля
есть 2-е таблицы Pervoe_vzveshivanie и Vtoroi_Ves. нужно найти сумму этих двух...

Нужен пример на JS для постраничного вывода запросов из БД Microsoft SQL Server 2000.
Люди, помогиет мне. Я уже просто устал. Нужен пример на JS для постраничного...

PHP + MS SQL. Как передать параметры в хранимую процедуру?
Как передать из PHP значения параметров для хранимой процедуры в MS SQL Server...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
20
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Рейтинг@Mail.ru