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

← →
AndrewK © (2003-12-23 09:50) [0]

Доброго времени суток, господа.

Подскажите, как можно написать процедуру с датасетом в описании.

Например, необходимо написать процедуру, которая формирует отчет по использованию каких-нибудь ТМЦ.

Если требуется вывести отчет по одной ТМЦ, то процедура будет иметь вид

GetReport @ID int, @DateStart datetime, @DateStop datetime

Если по всем, то

GetReport @DateStart datetime, @DateStop datetime

А как мне сделать так, чтобы отчет вернул выборку по нескольким позициям, которые я в клиенте отмечу в checkbox.

GetReport @IDList что-то, @DateStart datetime, @DateStop datetime

Пока использую следующую схему:

GetReport @IDList varchar(1000), @DateStart datetime, @DateStop datetime declare @SQL varchar(8000) select @SQL = "select * from Table where id in ("+@IDList+")" Exec(@SQL)

Но что-то мне не нравиться этот метод - за строками надо следить, да и с региональными настройками иногда проблемы, да и анализатор кода в данном случае не работает.

Вопрос собственно в том как в процедуру передать набор данных с кодами и как их потом можно использовать в теле процедуры.

← →
Shirson © (2003-12-23 09:59) [1]

Это очень больной вопрос.

Я докатился до такого же решения:
На клиенте отрабатывается некий query, который имеет тучу параметров, получаемых от пользователя и жужжит достаточно долго. В результате он выдаёт курсор. Этот курсор нужно передать sp... решение родилось такое - клиентская часть берёт все id, полученные на клиенте и формиует их в строку вида "(523,789,6546,55,87,14378,788,6979)" После чего отдаёт эту строку sp.
sp берёт строку и делает с ней ... именно:
select @SQL = "select * from Table where id in ("+@IDList+")"
Я решил это точно так же. Другого пути не узрел, хотя долго пытался.

← →
Nikolay M. © (2003-12-23 10:18) [2]

Да, динамический SQL - это не есть хорошо. Как вариант - можно с клиента заполнять ID-шками временную или псевдовременную таблицу и джойнить с ней нужную.

← →
Ega23 © (2003-12-23 10:21) [3]

Есть другое решение.
Заводишь ещё один параметр для процедуры @IDList varchar(8000)=""
На клиенте формируешь такую строку "523,789,6546,55,87,14378,788,6979"

А сам запрос внутри процедуры генеришь динамически, что-то типа:

declare @SelectSQL varchar (8000) Set @SelectSQL= "Select * from Table1 where 0=0" if @DateStart>0 Set @SelectSQL=@SelectSQL+" and "+ "DateStart>="+Cast(Cast(@DateStart as Int) as varchar)+" " if @DateStop>0 Set @SelectSQL=@SelectSQL+" and "+ "DateStop>="+Cast(Cast(@DateStop as Int) as varchar)+" "+ if @IDList<>"" Set @SelectSQL=@SelectSQL+" and "+ "ID in ("+@IDLIST+")" exec(@SelectSQL)

← →
Ega23 © (2003-12-23 10:23) [4]

Блин, первый раз пост до конца не догрузился. Он же это и использует...
Осечка вышла.
Кстати, а чем конкретно способ не нравиться?

← →
Nikolay M. © (2003-12-23 10:56) [5]

> Ega23 © (23.12.03 10:23) [4]
> Кстати, а чем конкретно способ не нравиться?

> exec(@SelectSQL)

← →
Sandman25 © (2003-12-23 10:57) [6]

Я извиняюсь, но в MSSQL нет временных таблиц?

← →
Ega23 © (2003-12-23 10:58) [7]

> > exec(@SelectSQL)

И что в этом страшного?

← →
Ega23 © (2003-12-23 11:00) [8]

> Я извиняюсь, но в MSSQL нет временных таблиц?

Насколько я понял автора, требуется получить результирующий запрос одной транзакцией. Можно двумя - см.
> Nikolay M. © (23.12.03 10:18) [2]

← →
AndrewK © (2003-12-23 11:23) [9]

Как я понял есть два основных варианта:
1) Динамически создавать запрос и выполнять его на сервере (самый короткий, но и самый незащищенный от ошибок метод)
2) Заполнение временной таблицы на сервере и передача в процедуре ссылку на данные в таблице.

Тогда такой вопрос, можно ли создать на MSSQL 2000 табличку, желательно где-нибудь в памяти или в системной временной таблице, к которой имел бы доступ пользователь с правами public и к которой имела бы доступ процедура?

← →
Ega23 © (2003-12-23 11:25) [10]

> самый короткий, но и самый незащищенный от ошибок метод

Почему он незащищен от ошибок?

← →
AndrewK © (2003-12-23 11:47) [11]

>> .. за строками надо следить, да и с региональными настройками иногда проблемы, да и анализатор кода в данном случае не работает..

← →
Sandman25 © (2003-12-23 11:50) [12]

+ индексы не используются. Представьте, если нужны данные с 100 разных id.

← →
Ega23 © (2003-12-23 11:53) [13]

> AndrewK © (23.12.03 11:47) [11]

Хорошо.
1. С точки зрения пользователя:
Ты выбрал в гриде несколько записей и теперь хочешь получить по ним какую-то информацию. Так?
2. С точки зрения реализации:
У тебя есть некий DataSet, который отображается в гриде, где у каждой записи есть её ID в базе.
Как ты собираешься передавать их во временную таблицу?

← →
Nikolay M. © (2003-12-23 11:55) [14]

> можно ли создать на MSSQL 2000 табличку, желательно где-нибудь
> в памяти или в системной временной таблице

CREATE TABLE ##table_name
создаст глобальную временную таблицу в базе tempdb.
Только потом не забыть полазить по SQL books, почитать про то, какое настоящее имя дается локальным и глобальным временным таблицам в рамках сессии и про их время жизни.

← →
Ega23 © (2003-12-23 11:57) [15]

> Sandman25 © (23.12.03 11:50) [12]
> + индексы не используются. Представьте, если нужны данные
> с 100 разных id.

Так какое решение? 100 транзакций с клиента, чтобы эти ID запихнуть в #xxx или парсить строку в процедуре и опять же из процедуры делать 100 вставок в #xxx или как ранее я предложил?
В принципе, запросы разные по "тяжести" бывают.

← →
АлексейК (2003-12-23 12:01) [16]

А что бы создать временную таблицу с данными, по заранее неизвестным параметрам, динамически скрипт создавать не нужно?
Тем более имя таблицы должно быть уникально. Это дополнительные блокировки и ресурсы сервера могут быть задействованы не самым оптимальным образом.
Так что динамический запрос в данном случае, достаточно оптимальный вариант.

← →
Sandman25 © (2003-12-23 12:02) [17]

[15] Ega23 © (23.12.03 11:57)

100 транзакций с клиента. Если это долго (хотя сомневаюсь), то можно записывать в таблицу сразу при выборе строки пользователем в гриде.
А если пользователь захочет 500 id передать? У Вас как параметр описан? Char(10000)? Стоит ли каждый раз такую длинную строку передавать? Особенно если обычно передаются всего несколько id?

← →
Polevi © (2003-12-23 12:12) [18]

CREATE FUNCTION IDsToTable (@IDs VARCHAR(4096))
RETURNS @T TABLE (ID INT) AS
BEGIN
DECLARE @cif INT
DECLARE @cit INT

IF @IDs IS NULL RETURN

SET @IDs=@IDs+","
SET @cif=1
SET @cit=CHARINDEX(",",@IDs,@cif)
while @cit<>0
begin
INSERT @T VALUES(Substring(@IDs,@cif,@cit-@cif))
SET @cif=@cit+1
SET @cit=CHARINDEX(",",@IDs,@cif)
end

RETURN
END

CREATE PROCEDURE GetReport @IDList varchar(1000), @DateStart datetime, @DateStop datetime
AS
SELECT * FROM Transacts T
JOIN IdsToTable(@IdList) IDT ON T.SomeField=IDT.ID
WHERE FactDate BETWEEN @DateStart AND @DateStop

> Sandman25 © (23.12.03 12:02) [17]
> [15] Ega23 © (23.12.03 11:57)
>
> 100 транзакций с клиента. Если это долго (хотя сомневаюсь),
> то можно записывать в таблицу сразу при выборе строки пользователем
> в гриде.
> А если пользователь захочет 500 id передать? У Вас как параметр
> описан? Char(10000)? Стоит ли каждый раз такую длинную строку
> передавать? Особенно если обычно передаются всего несколько
> id?

Не Char(10000) а varchar(8000)

Books OnLine:
varchar[(n)]
Variable-length non-Unicode character data with length of n characters. n must be a value from 1 through 8,000. Storage size is the actual length of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.

А сразу в таблицу писать... А если пользователь передумал? если он просто перебирает записи? Каждый раз транзакцию на удаление/добавление? И это быстрее?

[19] Ega23 © (23.12.03 12:57)

Я имел в виду при выборе строки для операции, а не просто на ней курсор находится. Могли бы и догадаться :)
Ладно, сдаюсь :) Если соединение медленное, то действительно парсинг строки намного эффективнее.

> Sandman25 © (23.12.03 13:13) [20]
> [19] Ega23 © (23.12.03 12:57)
>
> Я имел в виду при выборе строки для операции, а не просто
> на ней курсор находится. Могли бы и догадаться :)
> Ладно, сдаюсь :) Если соединение медленное, то действительно
> парсинг строки намного эффективнее.

Так что, резюме: От ситуации зависит?
Закрываем ветку?

поправте если не прав.
не совсем понятно что нужно автору.
1. Клинет указывает по каким полям фильтровать набор данных для выдачи в отчет. В этом случае все просто. передаем параметры процедуре и добавляем к селекту в виде
create procedure proc @param datetype = null where (Table.Field = @param) or (param is null)

2. Клиент указывает какие поля входят в отчет. Тгда ,ИМХО, надо создавать временную таблицу и выводить клиетнту select * из этой таблицы.
create procedure getreport @param1 varchar(50)= null, @datetype1 varchar(50)= null as Begin declare @CrtTabl varchar(8000) set @CrtTabl="create table #report ( " if @param1 is not null set @CrtTabl=@CrtTabl+ param1+" "+datetype1 +")" exec @CrtTabl set @CrtTabl = "insert into #report (@param1) select <значение для поля>" exec @CrtTabl select * from #report drop table #report END

соответсвенно если полей больше одного то формирование строки @CrtTabl несколько сложнее но принцип, думаю, понятен

[21] Ega23 © (23.12.03 13:17)

Так что, резюме: От ситуации зависит?

Конечно. Если нужно не id, а какие-нибудь сложные данные (таблица из нескольких полей, причем не только числовых, а если еще и с блобами), то передача данных через параметры проблематична.

> Sandman25 © (23.12.03 13:24) [23]
> [21] Ega23 © (23.12.03 13:17)
>
> Так что, резюме: От ситуации зависит?

Вопрос-то для меня принципиальный - я никогда не делал с временной таблицей, а всё время in использовал. Вот и хочу понять насколько этот метод хуже/лучше чем остальные вышеприведённые.

[24] Ega23 © (23.12.03 13:29)

Если передаются только целые числа, а затем в хранимой при необходимости(!) создается и заполняется временная таблица, то никаких проигрышей нет, а только выигрыши.

Я же никогда не использовал такую методику, но по объективным причинам - в Informix SPL нет нормальных функций для парсинга строк. Уже не говоря о dynamic SQL.

> Так что, резюме: От ситуации зависит?

Думаю, полностью готовое резюме написали в [18].
Бери и пользуйся.

Как правильно написать хранимую процедуру? Найти похожие ветки