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

Откуда: Москва
Сообщений: 1613
При переводе бэкенда базы с MS Access на SQL server 2017 с сохранением фронтенда на MDB, помимо прочих возникла проблема с перекрёстными запросами и зависимыми от них запросами.

Для самих перекрёстных запросов написана базовая функция msrTransformAsAccessDo, которая используя динамический SQL возвращает набор записей:
+ msrTransformAsAccessDo
CREATE PROCEDURE [dbo].[msrTransformAsAccessDo]
	@TRANSFORM_Function nvarchar(max),
	@TRANSFORM_Field nvarchar(max),
	@SQL_SELECT nvarchar(max),
	@SQL_FROM_WHERE nvarchar(max),
	@SQL_GROUPBY_HAVING_ORDERBY nvarchar(max),
	@PIVOTBY nvarchar(max) 
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @DynSQL nvarchar(max)
	SET @DynSQL = N' SELECT DISTINCT ' + @PIVOTBY + ' as key_value into ##pivoting ' + @SQL_FROM_WHERE + ' ORDER BY ' + @PIVOTBY
	DROP TABLE IF EXISTS ##pivoting
	EXEC (@DynSQL)
	DECLARE @Values nvarchar(max) = N''
	DECLARE @tmpStr nvarchar(max)
	DECLARE @rsk CURSOR         
	SET @rsk = CURSOR SCROLL
		FOR	
			select key_value from ##pivoting
	OPEN @rsk         
		FETCH NEXT FROM @rsk INTO @tmpStr
		WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @Values = @Values + ', ' + @TRANSFORM_Function + '(Case When convert(nvarchar,' + @PIVOTBY + ')=convert(nvarchar,''' + @tmpStr + ''') Then ' + @TRANSFORM_Field + ' Else Null End) AS [' + @tmpStr + '] '
				FETCH NEXT FROM @rsk INTO @tmpStr
			END
	CLOSE @rsk
	DROP TABLE IF EXISTS ##pivoting
	SET @DynSQL = @SQL_SELECT + @Values + @SQL_FROM_WHERE + @SQL_GROUPBY_HAVING_ORDERBY
	EXEC (@DynSQL)
END

И для каждого из перекрёстных запросов по функции обёртке с вызовом:
CREATE PROCEDURE [dbo].[TestPIVOT]
AS
BEGIN
	SET NOCOUNT ON;
	EXEC msrTransformAsAccessDo	 
	  'Sum', 'qty'
	  ,'SELECT SeenAt, stockid ', 'FROM tbl_test ', 'GROUP BY SeenAt, stockid ' 
	  , 'shelfid'
END

Тестовая таблица
+ tbl_test
CREATE TABLE tbl_test
(
    SeenAt  date,
    stockid  int,
    shelfid  int,
    qty  float
);

INSERT INTO tbl_test
(
    SeenAt,
    stockid,
    shelfid,
    qty
) VALUES
('2018-03-01',1000,1,24.8),

('2018-03-02',1000,1,26.2),
('2018-03-02',1000,2,11.3),
('2018-03-02',1000,3,45.0),

('2018-03-03',1000,1,27.8),
('2018-03-03',1000,3,87.1),

('2018-03-04',1000,1,28.8),
('2018-03-04',1000,2,31.8),

('2018-03-05',1000,1,21.8),
('2018-03-06',1000,4,23.8),

('2018-03-01',2000,1,30.89),
('2018-03-02',2000,2,33.00),
('2018-03-03',2000,1,34.8),
('2018-03-04',2000,2,34.2),
('2018-03-05',2000,1,33.8),
('2018-03-06',2000,2,43.8);

Соответственно результат самого перекрёстного запроса получить легко:
EXECUTE [dbo].[TestPIVOT] 

SeenAt  	stockid	1	2	3	4
2018-03-01 1000 24.8 NULL NULL NULL
2018-03-02 1000 26.2 11.3 45 NULL
2018-03-03 1000 27.8 NULL 87.1 NULL
2018-03-04 1000 28.8 31.8 NULL NULL
2018-03-05 1000 21.8 NULL NULL NULL
2018-03-06 1000 NULL NULL NULL 23.8
2018-03-01 2000 30.89 NULL NULL NULL
2018-03-02 2000 NULL 33 NULL NULL
2018-03-03 2000 34.8 NULL NULL NULL
2018-03-04 2000 NULL 34.2 NULL NULL
2018-03-05 2000 33.8 NULL NULL NULL
2018-03-06 2000 NULL 43.8 NULL NULL
Но в Access'е этот результат используется в JOIN'ах множества других запросов.
В FAQ :Select из результата выполнения хранимой процедуры описано 2 варианта. Первый - "Оформить хранимую процедуру как функцию", не подходит т.к. количество столбцов неизвестно, второй - "Получить набор через временную таблицу" - потребует превратить все зависимые запросы в хранимые процедуры с динамическим SQL и потребует написания сложной программы создающей тексты этих процедур.

Подскажите, какие есть ещё варианты?
26 сен 18, 11:19    [21686452]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
4d_monster,

OPENQUERY
но смысл перехода с таким подходом теряется
26 сен 18, 11:29    [21686467]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
4d_monster
Member

Откуда: Москва
Сообщений: 1613
TaPaK,

К сожалению OPENQUERY и openrowset не работают с динамическим SQL:
select * from openquery ( [LOOPBACK],  'exec testDB.dbo.[TestPIVOT]' )

Msg 11514, Level 16, State 1, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 0]
Не удалось определить метаданные, поскольку инструкция "EXEC (@DynSQL)" в процедуре "msrTransformAsAccessDo" содержит динамический код SQL. Рекомендуется использовать предложение WITH RESULT SETS, чтобы явно описать результирующий набор.
26 сен 18, 12:06    [21686515]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
KRS544
Member

Откуда:
Сообщений: 497
set @Query= 'ваш запрос'
EXEC ('select * from openquery ( [LOOPBACK],  '''+@Query+''' )')
26 сен 18, 14:30    [21686790]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
aleks222
Member

Откуда:
Сообщений: 951
Есть ажно три пути.
1. Разучить таки родной pivot MS SQL.
2. В процедуре засовывать результат во временную таблицу и возвращать селект из времянки.
3. Забить на это фуфло.
26 сен 18, 14:33    [21686797]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
4d_monster
Member

Откуда: Москва
Сообщений: 1613
KRS544,
А как этот результат использовать в качестве источника для SELECT?

aleks222,

1. А где прочитать как родной pivot MS SQL поддерживает динамические столбцы?
2. этот вариант описан в вопросе, он резервный.
3. это ещё недостаточно большая проблема, чтобы отказываться от SQL server
26 сен 18, 14:59    [21686836]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
KRS544
Member

Откуда:
Сообщений: 497
4d_monster
KRS544,
А как этот результат использовать в качестве источника для SELECT?


insert into Table
exec ('...')
26 сен 18, 15:01    [21686839]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
Sintetik
Member

Откуда: SpB->Msk->...
Сообщений: 9304
не хочу создавать тему, спрошу здесь, т.к. очень похоже

Есть хранимые процедуры, возвращающие таблицу, у процедур могут быть параметры, менять процедуры нельзя
нужно получить возможность делать SELECT * FROM <процедура> из клиентов,
т.е. процедуру можно обернуть любым скриптом, но клиент не может выполнять скрипты, только запросы

попробовал обернуть в функцию с временной таблицей но получил
1) [Code: 2772, SQL State: S1000] Cannot access temporary tables from within a function.
26 сен 18, 15:13    [21686862]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
Sintetik
не хочу создавать тему, спрошу здесь, т.к. очень похоже
А предложенное решение вам не подходит? Или вы не прочитали ответы?
26 сен 18, 16:26    [21686996]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
Sintetik
Member

Откуда: SpB->Msk->...
Сообщений: 9304
alexeyvg
Sintetik
не хочу создавать тему, спрошу здесь, т.к. очень похоже
А предложенное решение вам не подходит? Или вы не прочитали ответы?

читал, которое?
26 сен 18, 17:48    [21687176]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
Sintetik
alexeyvg
пропущено...
А предложенное решение вам не подходит? Или вы не прочитали ответы?

читал, которое?
Вот это:
select * from openquery ( [LOOPBACK],  'exec testDB.dbo.[TestPIVOT]' )

Если, конечно, у вас нет динамического SQL. Но вы про него не писали.
Если есть, то решения не существует.
27 сен 18, 02:12    [21687631]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
Sintetik
попробовал обернуть в функцию с временной таблицей но получил
1) [Code: 2772, SQL State: S1000] Cannot access temporary tables from within a function.
В функции можно использовать таблицы-переменные.
27 сен 18, 02:14    [21687632]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
Sintetik
Member

Откуда: SpB->Msk->...
Сообщений: 9304
alexeyvg
Sintetik
пропущено...

читал, которое?
Вот это:
select * from openquery ( [LOOPBACK],  'exec testDB.dbo.[TestPIVOT]' )

Если, конечно, у вас нет динамического SQL. Но вы про него не писали.
Если есть, то решения не существует.

динамического нет, но в этом варианте нужно в строку вызова передавать логин-пароль, по крайней мере в примере выше так было, поэтому я откинул этот вариант, или можно обойтись без передачи?
27 сен 18, 10:47    [21687852]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
Sintetik
Member

Откуда: SpB->Msk->...
Сообщений: 9304
Sintetik
динамического нет, но в этом варианте нужно в строку вызова передавать логин-пароль, по крайней мере в примере выше так было, поэтому я откинул этот вариант, или можно обойтись без передачи?

а нет, значит в другом примере видел, когда рыскал в поисках решения
27 сен 18, 10:49    [21687857]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
Sintetik
Sintetik
динамического нет, но в этом варианте нужно в строку вызова передавать логин-пароль, по крайней мере в примере выше так было, поэтому я откинул этот вариант, или можно обойтись без передачи?

а нет, значит в другом примере видел, когда рыскал в поисках решения
Нет, это для OPENDATASOURCE нужно, да и то не всегда.
А для OPENQUERY никогда не нужно.
27 сен 18, 11:00    [21687883]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1723
Sintetik
не хочу создавать тему, спрошу здесь, т.к. очень похоже

Есть хранимые процедуры, возвращающие таблицу, у процедур могут быть параметры, менять процедуры нельзя
нужно получить возможность делать SELECT * FROM <процедура> из клиентов,
т.е. процедуру можно обернуть любым скриптом, но клиент не может выполнять скрипты, только запросы

попробовал обернуть в функцию с временной таблицей но получил
1) [Code: 2772, SQL State: S1000] Cannot access temporary tables from within a function.


FAQ почитайте: [url=]https://www.sql.ru/faq/faq_topic.aspx?fid=416[/url]
27 сен 18, 11:10    [21687909]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
Sintetik
Member

Откуда: SpB->Msk->...
Сообщений: 9304
alexeyvg
Sintetik
пропущено...

читал, которое?
Вот это:
select * from openquery ( [LOOPBACK],  'exec testDB.dbo.[TestPIVOT]' )

Если, конечно, у вас нет динамического SQL. Но вы про него не писали.
Если есть, то решения не существует.


автор
[Code: 7202, SQL State: S1000] Could not find server 'LOOPBACK' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
27 сен 18, 16:18    [21688340]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Sintetik
alexeyvg
пропущено...
Вот это:
select * from openquery ( [LOOPBACK],  'exec testDB.dbo.[TestPIVOT]' )

Если, конечно, у вас нет динамического SQL. Но вы про него не писали.
Если есть, то решения не существует.


автор
[Code: 7202, SQL State: S1000] Could not find server 'LOOPBACK' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

линк кто за вас создать? справку о команде тоже лень прочитать? Сразу на форум строчить
27 сен 18, 16:20    [21688345]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
Sintetik
автор
[Code: 7202, SQL State: S1000] Could not find server 'LOOPBACK' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Да, вот что написано, то и нужно сделать.
Или в графическом интерфейсе, если вам так удобнее.
27 сен 18, 16:48    [21688376]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
Sintetik
Member

Откуда: SpB->Msk->...
Сообщений: 9304
TaPaK
линк кто за вас создать? справку о команде тоже лень прочитать? Сразу на форум строчить

я полагал, что есть по умолчанию стандартный линк сам на себя с таким именем LOOPBACK
27 сен 18, 17:09    [21688389]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
Sintetik
я полагал, что есть по умолчанию стандартный линк сам на себя с таким именем LOOPBACK
Нет, увы.
Просто непонятно, зачем он нужен, ведь любой динамический запрос можно сделать просто в EXEC, всё таки такое редко встречается, что нужно непременно слово SELECT.
27 сен 18, 17:48    [21688417]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
AndrF
Member

Откуда:
Сообщений: 2194
4d_monster,

Как вариант - пускай ваша процедура только строит строку запроса, но не запускает, а просто возвращает вам ее, ну а далее вы в динамике делаете с ней что хотите...
27 сен 18, 23:48    [21688616]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
4d_monster
Member

Откуда: Москва
Сообщений: 1613
AndrF,

Получается есть только два варианта:
1. создавать временные таблицы
2. использовать динамический текст запроса.

И оба варианта требуют превращения запросов, зависимых от перекрёстного, в хранимые процедуры.

Проверю, какой проще в реализации и выберу его.
28 сен 18, 08:40    [21688709]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3618
4d_monster
AndrF,

Получается есть только два варианта:
1. создавать временные таблицы
2. использовать динамический текст запроса.

И оба варианта требуют превращения запросов, зависимых от перекрёстного, в хранимые процедуры.

Проверю, какой проще в реализации и выберу его.

Есть еще самый нормальный вариант - изменить процедуру внутри, чтобы она результат сама в таблицу писала
Кто ж вам доктор, что у вас по нормальному нельзя
28 сен 18, 12:22    [21688950]     Ответить | Цитировать Сообщить модератору
 Re: Использовать результат динамического запроса хранимой процедуры в запросе или VIEW  [new]
4d_monster
Member

Откуда: Москва
Сообщений: 1613
Ivan Durak
4d_monster
AndrF,

Получается есть только два варианта:
1. создавать временные таблицы
2. использовать динамический текст запроса.

И оба варианта требуют превращения запросов, зависимых от перекрёстного, в хранимые процедуры.

Проверю, какой проще в реализации и выберу его.

Есть еще самый нормальный вариант - изменить процедуру внутри, чтобы она результат сама в таблицу писала

Поля не определены заранее ведь это динамический перекрёстный запрос, поэтому ваше предложение это 1 пункт.

Ivan Durak
Кто ж вам доктор, что у вас по нормальному нельзя

"Legacy" знаете? Тамошние мы.
28 сен 18, 12:55    [21689005]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить