Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Вопросы новичка к уважаемым экспертам  [new]
studieren
Member

Откуда: Tashkent, Uzbekistan
Сообщений: 2845
Хочу создать скалярную функцию, у которой единственный параметр "@SQL Varchar(MAX)".
Функция должна "попытаться" извлечь результирующее значение SQL запроса (т.е. использовать EXECUTE внутри TRY ... CATCH ...).
Если произойдёт ошибка, то функция возвращает текст ошибки.
Если всё О.К., то результирующее значение SQL запроса.

Хотел всё это создать таким образом:
CREATE FUNCTION dbo.f_ValueOfExecutedQuery_Text
(@SQL AS Varchar(MAX))
/*	Функция исполнит указанный запрос и возвратит полученное
	значение.
*/
RETURNS Varchar(MAX)
AS
BEGIN
	DECLARE @Text AS Varchar(MAX);
	DECLARE @Value AS Table (Value Varchar(MAX));

	IF @SQL Is Null OR LEN(@SQL) = 0
	GOTO TheEnd;

	BEGIN TRY
		INSERT INTO @Value (Value)
		EXECUTE (@SQL);
	END TRY
	BEGIN CATCH
		SET @Text = 'Error number: ' + CAST(ERROR_NUMBER() AS Varchar(20)) +
			'   Error message: ' + ERROR_MESSAGE();
		GOTO TheEnd;
	END CATCH

	SELECT @Text = Value FROM @Value;

TheEnd:
	RETURN @Text;
END

Но SQL ругается, во-первых не хочет видеть "TRY ... CATCH ..." и во-вторых "не терпит" EXECUTE внутри функции. Где-то в справочниках я видел, что нельзя так.
Но тем не менее мне всё равно такая функция нужна для решения одной частной задачи.
Как мне поступить? Превратить всё это сначала на ХП и потом из ХП достать? Или каким-то образом можно OPENROWSET использовать без дополнительной ХП?
Please подскажите.
3 дек 10, 09:33    [9879250]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
1. Динамический SQL недопустим в функции.
2. TRY...CATCH недопустим в функциях.
3. Смысл такой универсальной функции - не понятен.
3 дек 10, 10:07    [9879451]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
studieren
Member

Откуда: Tashkent, Uzbekistan
Сообщений: 2845
pkarklin
1. Динамический SQL недопустим в функции.
2. TRY...CATCH недопустим в функциях.
3. Смысл такой универсальной функции - не понятен.


В том то и дело, что нельзя. А мне надо как-то обойти это.
А смысл в следующем:
у меня есть таблица для шаблонов контракта, где в отдельном поле я создал SQL запросы.
Когда юзер захочет открыть отчёт "Контракт", то SQL должен найти название фирмы, реквизиты и т.п. и подставлять в текст контракта. Для этого и запускается все SQL запросы из таблицы с шаблонами.
Сейчас я пользуюсь циклом и один за другим перебираю значения, а потом EXECUTE. Вот если бы у меня была такая скалярная функция, то я смог бы одним запросом получить все данные в готовом виде.
Понимаете?
3 дек 10, 10:20    [9879554]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
Glory
Member

Откуда:
Сообщений: 104751
studieren

В том то и дело, что нельзя. А мне надо как-то обойти это.

Обойти не получится. Придется менять подход к решению задачи


studieren
Когда юзер захочет открыть отчёт "Контракт", то SQL должен найти название фирмы, реквизиты и т.п. и подставлять в текст контракта. Для этого и запускается все SQL запросы из таблицы с шаблонами.
Сейчас я пользуюсь циклом и один за другим перебираю значения, а потом EXECUTE. Вот если бы у меня была такая скалярная функция, то я смог бы одним запросом получить все данные в готовом виде.
Понимаете?

И зачем динамический запрос нужен для замены значений в поле таблицы или результа запроса ?
3 дек 10, 10:25    [9879591]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
studieren
pkarklin
1. Динамический SQL недопустим в функции.
2. TRY...CATCH недопустим в функциях.
3. Смысл такой универсальной функции - не понятен.


В том то и дело, что нельзя. А мне надо как-то обойти это.
А смысл в следующем:
у меня есть таблица для шаблонов контракта, где в отдельном поле я создал SQL запросы.
Когда юзер захочет открыть отчёт "Контракт", то SQL должен найти название фирмы, реквизиты и т.п. и подставлять в текст контракта. Для этого и запускается все SQL запросы из таблицы с шаблонами.
Сейчас я пользуюсь циклом и один за другим перебираю значения, а потом EXECUTE. Вот если бы у меня была такая скалярная функция, то я смог бы одним запросом получить все данные в готовом виде.
Понимаете?
Обычно сначала изучают возможности того программного инструмента, на котором программируют.
А программирую уже потом. Чтобы не закладывать в свои идеи невыполнимые требования.
Уже в момент проектирования чего-либо в голове должен созреть план совершенно конкретной реализации.
Понимаете?
3 дек 10, 10:29    [9879626]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
лолл
Member

Откуда:
Сообщений: 450
studieren,

бред полный. кроме того вы пытаетесь допустить sql injection...
3 дек 10, 10:34    [9879669]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
studieren,

ох уж ети сказочники , ох уж ети сказочки
НО ,за пятничное настроение - спасибо ,порадовали
3 дек 10, 10:36    [9879676]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
studieren
Member

Откуда: Tashkent, Uzbekistan
Сообщений: 2845
Glory,

Чтобы более понятнее было может быть мне стоит сначала объяснить. О.К.!

Есть таблица "Контракт", где есть поле "Код шаблона".
Есть таблица "Шаблон", где хранятся шаблонные тексты контракта, а также есть у некоторых записях SQL запрос.
Я не могу сразу сохранить название фирмы и ее реквизиты в шаблонных текстах, иначе мне придётся слишком много шаблонов создать. Как Вы знаете в контрактах есть стандартные пункты, которых не надо менять. Они сразу выходят в отчёт (т.е. у таких записях поле "SQL запрос" пустое).
А вот есть и такие пункты (скажем, реквизиты) где SQL сам должен найти нужные значения и в каждом случае отдельно подставить в текст "Контракта". Так вот именно для таких случаях я и создал универсальные SQL запросы. Сейчас я пользуюсь циклом и один за другим перебираю значения поле "SQL запрос" и только потом выполняю EXECUTE.
Мне хотелось бы всё это получить одним запросом без циклов и курсоров. А для этого мне не хватает скалярной функции.

Очень надеюсь, что теперь полностью объяснил мой замысел.

Если я не прав, то Please подтолкните в нужное направление. Может я базу спроектировал не так!
3 дек 10, 10:37    [9879685]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
лолл
Member

Откуда:
Сообщений: 450
studieren,

откажитесь от универсальных запросов, напишите хранимую процедуру, которая в зависимости от указанного вами шаблона выдает нужный вам результат.
3 дек 10, 10:41    [9879711]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
Glory
Member

Откуда:
Сообщений: 104751
studieren
G
А вот есть и такие пункты (скажем, реквизиты) где SQL сам должен найти нужные значения и в каждом случае отдельно подставить в текст "Контракта". Так вот именно для таких случаях я и создал универсальные SQL запросы. Сейчас я пользуюсь циклом и один за другим перебираю значения поле "SQL запрос" и только потом выполняю EXECUTE.

Вы для замены подстроки в строке используете EXECUTE ???
3 дек 10, 10:41    [9879712]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
studieren
Если я не прав, то Please подтолкните в нужное направление. Может я базу спроектировал не так!


в яблочко с вашей архитектурой уже на етапе заполнения траблы с реализацией,а теперь на секунду представтье.... например оборотно сальдовую ведомость или запрос c пивотом, или отчет о контрагентах.......
3 дек 10, 10:41    [9879717]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
ё
Guest
studieren
...
Есть таблица "Контракт", где есть поле "Код шаблона".
Есть таблица "Шаблон", где хранятся шаблонные тексты контракта, а также есть у некоторых записях SQL запрос.
...

а вы не могли бы, проилюстрировать, вот это, каким нибудь примерчиком ?

и вообще, шаблон, он на то и шаблон, что бы в нём какоито обобщенное значение ([$FirmName$]) менять на конкретное/заданное (МаяФирма), и всё это запросто можно сделать в запросе, без всякой динамики и функций
3 дек 10, 10:52    [9879825]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
nosov
Guest
автор
Я не могу сразу сохранить название фирмы и ее реквизиты в шаблонных текстах
создайте отдельную таблицу-справочник "Конрагенты"
F1 -- название
F2 -- реквизиты

и до применения БД при работе с WORD
реквизиты хранили отдельно
шаблоны текстов контрактов отдельно
3 дек 10, 10:56    [9879863]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
studieren
Member

Откуда: Tashkent, Uzbekistan
Сообщений: 2845
Glory
studieren
G
А вот есть и такие пункты (скажем, реквизиты) где SQL сам должен найти нужные значения и в каждом случае отдельно подставить в текст "Контракта". Так вот именно для таких случаях я и создал универсальные SQL запросы. Сейчас я пользуюсь циклом и один за другим перебираю значения поле "SQL запрос" и только потом выполняю EXECUTE.

Вы для замены подстроки в строке используете EXECUTE ???


Вот как я сейчас делаю.
Создал ХП, где сначала собираю SQL запросы (если есть) в табличный переменный.
Циклом получаю один за другим и передаю значение переменному @Text.
Затем
INSERT #Tbl (Value)
EXECUTE @Text;
А потом заменяю SQL запрос на найденное значение в табличном переменном.
Далее, вывожу отчёт вместе как "фиксированные", так и "найденные" значения.
Вообщем то всё получается как надо. Но только уж больно сложная ХП у меня получается.
Хотелось бы упрастить.
3 дек 10, 10:56    [9879864]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
Glory
Member

Откуда:
Сообщений: 104751
studieren
Хотелось бы упрастить.

Не надо рассказывать свой способ решения
Нужно показать входные данные, логику и конечный результат
3 дек 10, 11:01    [9879908]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
studieren,

самый тупой и простой одновременно способ заполнения шаблона
SELECT 
replace(replace(text_shblon,'@@name contragent@@', ct.name),'@@address contragent@@', ct.address)
FROM shablons sh
cross JOIN contragents ct
WHERE sh = 1 AND ct = 1
идея понятна?:)
3 дек 10, 11:09    [9879990]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
studieren
Member

Откуда: Tashkent, Uzbekistan
Сообщений: 2845
ё
studieren
...
Есть таблица "Контракт", где есть поле "Код шаблона".
Есть таблица "Шаблон", где хранятся шаблонные тексты контракта, а также есть у некоторых записях SQL запрос.
...

а вы не могли бы, проилюстрировать, вот это, каким нибудь примерчиком ?

и вообще, шаблон, он на то и шаблон, что бы в нём какоито обобщенное значение ([$FirmName$]) менять на конкретное/заданное (МаяФирма), и всё это запросто можно сделать в запросе, без всякой динамики и функций


WarAnt
самый тупой и простой одновременно способ заполнения шаблона

SELECT
replace(replace(text_shblon,'@@name contragent@@', ct.name),'@@address contragent@@', ct.address)
FROM shablons sh
cross JOIN contragents ct
WHERE sh = 1 AND ct = 1

идея понятна?:)


И как Вы это представляете? Расплодить Replace многократно? Типа так
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Fragment, '[НазваниеФирмы]', Company), '[НазваниеИнофирмы]', ForeignCompany) ...
FROM ...

Примерно также, адрес фирмы, адрес инопартнёра, название банка фирмы, название банка инопартнёра, валюта контракта, сумма контракта, условия поставки, пункт назначения и т.д. и т.п.
И сколько же Replace будет у меня? Штук минимум 20 если не больше. А ведь для каждого фрагмента шаблонного текста не требуется столько!
Есть ещё нюанс! Некоторые шаблонные тексты нельзя формировать прямо из базы данных, их должен юзер подкорректировать слегка. Вы знаете свойство русского языка (склонение по падежам и прочие, прочие). Вот они у меня ещё хранятся в отдельной таблице. Я и их учитываю, ибо контракт - официальный документ. Орфографические и прочие лингвистические ошибки не допускается. Конечно мне легче было бы тупо заменить все подряд и пускай расплодить REPLACE хоть 100 штук. Но такое решение юзеров не устраивает!

Итак, мы отошли от темы. Никто не поможет со скалярной функцией?
3 дек 10, 12:30    [9880864]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
Glory
Member

Откуда:
Сообщений: 104751
studieren
И как Вы это представляете? Расплодить Replace многократно? Типа так
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Fragment, '[НазваниеФирмы]', Company), '[НазваниеИнофирмы]', ForeignCompany) ...
FROM ...

Что, типа некрасиво ?
Просто интересно, зачем вы REPLACE в динамике то выполняли ?

studieren
Никто не поможет со скалярной функцией?

Невозможна в функции динамики
Совсем невозможна
Совсем-совсем
3 дек 10, 12:35    [9880938]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
studieren
Member

Откуда: Tashkent, Uzbekistan
Сообщений: 2845
Glory
studieren
И как Вы это представляете? Расплодить Replace многократно? Типа так
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Fragment, '[НазваниеФирмы]', Company), '[НазваниеИнофирмы]', ForeignCompany) ...
FROM ...

Что, типа некрасиво ?
Просто интересно, зачем вы REPLACE в динамике то выполняли ?

studieren
Никто не поможет со скалярной функцией?

Невозможна в функции динамики
Совсем невозможна
Совсем-совсем


Ладно, как-нибудь найду путь обхода. Надеюсь у меня получится и обязательно здесь опубликую. Может быть не скоро, но я всё равно найду.

Знаете слова Эйнштейна?
Все знают, что это невозможно. Но случайно находится невежда, который этого не знает. Он-то и делает открытие.. ((С) А. Эйнштейн)
3 дек 10, 12:46    [9881080]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
studieren
Glory
пропущено...

Что, типа некрасиво ?
Просто интересно, зачем вы REPLACE в динамике то выполняли ?

пропущено...

Невозможна в функции динамики
Совсем невозможна
Совсем-совсем


Ладно, как-нибудь найду путь обхода. Надеюсь у меня получится и обязательно здесь опубликую. Может быть не скоро, но я всё равно найду.

Знаете слова Эйнштейна?
Все знают, что это невозможно. Но случайно находится невежда, который этого не знает. Он-то и делает открытие.. ((С) А. Эйнштейн)
Совсем, что ли, полезным нечем заняться? Везет.
3 дек 10, 12:47    [9881097]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
ё
Guest
studieren
И как Вы это представляете? Расплодить Replace многократно? Типа так
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Fragment, '[НазваниеФирмы]', Company), '[НазваниеИнофирмы]', ForeignCompany) ...
FROM ...
...
Конечно мне легче было бы тупо заменить все подряд и пускай расплодить REPLACE хоть 100 штук. Но такое решение юзеров не устраивает!

да хоть 200-ти! если это лучше
пугает в запросе - оформите функцией - одной для всех шаблонов/НЕ одной, это уже варианты
зато всё будет четко и понятно - добавите новый реквизит - нужно будет изменить одну функцию, а не дцать динамических запросов, хранящихся фик знает где (пример то вы не привели...)

и причём тут юзеры? они что у вас запросы пишут

studieren
Есть ещё нюанс! Некоторые шаблонные тексты нельзя формировать прямо из базы данных, их должен юзер подкорректировать слегка. Вы знаете свойство русского языка (склонение по падежам и прочие, прочие). Вот они у меня ещё хранятся в отдельной таблице. Я и их учитываю, ибо контракт - официальный документ. Орфографические и прочие лингвистические ошибки не допускается.

типа, динамический запрос в функции - исправляет ошибки ? так что ли ?
"(склонение по падежам и прочие, прочие)" - это отдельная не мелкая тема,
и не стоит это пока смешивать
3 дек 10, 12:49    [9881118]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
Glory
Member

Откуда:
Сообщений: 104751
studieren
Знаете слова Эйнштейна?
Все знают, что это невозможно. Но случайно находится невежда, который этого не знает. Он-то и делает открытие.. ((С) А. Эйнштейн)

Эйнштейн говорил про вообще неоткрытые наукой вещи
А тут производители сознательно ограничили синтаксис функции
О чем честно написали в руководстве
Читайте его
Лучше до того, как будете выбирать способ решения своей следующей задачи
3 дек 10, 12:49    [9881125]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Glory
studieren
Знаете слова Эйнштейна?
Все знают, что это невозможно. Но случайно находится невежда, который этого не знает. Он-то и делает открытие.. ((С) А. Эйнштейн)

Эйнштейн говорил про вообще неоткрытые наукой вещи
А тут производители сознательно ограничили синтаксис функции
О чем честно написали в руководстве
Читайте его
Лучше до того, как будете выбирать способ решения своей следующей задачи
Кстати говоря, ТС решил по заветам Эйнштейна найти неизвестный ещё людям баг.
Когда MS об этом узнает и исправит его, будет настоящая драма!
3 дек 10, 12:57    [9881233]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
лолл
Member

Откуда:
Сообщений: 450
studieren,

как вариант:
1. создаем представление, которое получает в виде полей всю динамически меняющуюся информацию (наименование контрагента, адрес, сумма и т.п.).
2. сохраняем шаблон в таблицу примерно такой структуры:
create table Template(
  ID int not null,
  Type int not null, -- Тип строки шаблона: текст/запрос
  Value nvarchar(8000) not null -- Значение строки
)
в более сложных схемах построения можно пользоваться, например, не таблицей или таблицами, а xml.
3. склеиваем динамический запрос на основе данных таблицы шаблона и представления в строковую переменную @SQL так, чтобы в ней сохранилось нечто вроде:
N'select @Query = N''Контрагент = '' + DataView.ContragentName + ...
from DataView -- Представление из п.1
where <условие выбора для представления>'
в данном случае строки 'Контрагент = ' и DataView.ContragentName хранятся в Template под разными типами Type.
4. выполняем через exec sp_executesql @SQL, '@Query nvarchar(max) output', @SQL output
в @SQL получили строку, склеенную из статичных тесктов шаблона и данных представления.

может криво объяснил, надеюсь, подход понятен.
3 дек 10, 13:05    [9881347]     Ответить | Цитировать Сообщить модератору
 Re: Вопросы новичка к уважаемым экспертам  [new]
studieren
Member

Откуда: Tashkent, Uzbekistan
Сообщений: 2845
ё,

Скажите ё, а множества REPLACE не сильно будет сказаться на производительность? Ведь для него я должен буду очень развернутое представление (или функцию) писать, чтобы все эти реквизиты вытащить.
В принципе я сейчас начинаю понимать, что такой подход кажется лучше. Юзеры как я не умеют писать запросы. Я такой вариант не рассматривал раньше из-за множества REPLACE.
3 дек 10, 13:17    [9881498]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить