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

Откуда:
Сообщений: 7915
Цель: создать функцию, получающая 2 входных параметра и возвращающее табличное значение.
Имеется прилинкованный Oracle-сервер. Из него и хочу вытянуть данные.

Для начала создал тестовый пример для проверки работоспособности:
+
DECLARE @type varchar(1) = 'Q', @ident varchar(6) = '000697'; -- Как бы входные параметры

DECLARE @t_sql varchar(max) = 'SELECT DATE,SDATE,YEAR,MONTH,QUARTER,TYPE,VALUE FROM OPENQUERY(NFS_FOND, ''SELECT "DATE",
       SDATE,
       "YEAR",
       "MONTH",
       QUARTER,
       "TYPE",
       "VALUE"
  FROM (
        SELECT TO_DATE (''''0101'''' || TY.SHOWING_DATE, ''''ddmmyyyy'''') AS "DATE",
               TO_CHAR (TY.SHOWING_DATE) AS "SDATE",
               TY.SHOWING_DATE AS "YEAR",
               NULL AS "MONTH",
               NULL AS "QUARTER",
               ''''Y'''' AS "TYPE",
               TY.FACT_01 AS "VALUE",
               TY.REG_NUMBER AS RN
          FROM SHOWING_VALUES_21_Y ty
        /*quarters*/
        UNION ALL
................................................
................................................
................................................
        SELECT TO_DATE (''''0111'''' || Tm.SHOWING_DATE, ''''ddmmyyyy'''') AS "DATE",
               ''''ноябрь '''' || Tm.SHOWING_DATE AS "SDATE",
               NULL AS "YEAR",
               11 AS "MONTH",
               NULL AS "QUARTER",
               ''''M'''' AS "TYPE",
               tm.FACT_NI_11 AS "VALUE",
               TM.REG_NUMBER AS RN
          FROM SHOWING_VALUES_21_m tm
        UNION ALL
        SELECT TO_DATE (''''0112'''' || Tm.SHOWING_DATE, ''''ddmmyyyy'''') AS "DATE",
               ''''декабрь '''' || Tm.SHOWING_DATE AS "SDATE",
               NULL AS "YEAR",
               12 AS "MONTH",
               NULL AS "QUARTER",
               ''''M'''' AS "TYPE",
               tm.FACT_NI_12 AS "VALUE",
               TM.REG_NUMBER AS RN
          FROM SHOWING_VALUES_21_m tm) tyqm
WHERE tyqm.TYPE = UPPER (''''' + @type + ''''')
       AND RN IN (SELECT reg_number
                    FROM showing_list_21
                   WHERE ident = ''''' + @ident + ''''')
'')';
SELECT @t_sql;
EXEC (@t_sql);

Отработал правильно. Можно см. скрин-шот...
Теперь хочу чтобы это организовать в табличную функцию...
+
ALTER FUNCTION [dbo].[F_POK_DATA] (@type varchar(1), @ident varchar(6))
RETURNS @t_IndicatorData TABLE 
(
    DATE datetime,
    SDATE varchar(200),
    YEAR int,
    MONTH int,
    QUARTER int,
    TYPE varchar(1),
    VALUE float
) 
AS
BEGIN
	DECLARE @t_sql varchar(max) = 'SELECT DATE,SDATE,YEAR,MONTH,QUARTER,TYPE,VALUE FROM OPENQUERY(NFS_FOND, ''SELECT "DATE",
		   SDATE,
		   "YEAR",
		   "MONTH",
		   QUARTER,
		   "TYPE",
		   "VALUE"
	  FROM (
			SELECT TO_DATE (''''0101'''' || TY.SHOWING_DATE, ''''ddmmyyyy'''') AS "DATE",
				   TO_CHAR (TY.SHOWING_DATE) AS "SDATE",
				   TY.SHOWING_DATE AS "YEAR",
				   NULL AS "MONTH",
				   NULL AS "QUARTER",
				   ''''Y'''' AS "TYPE",
				   TY.FACT_01 AS "VALUE",
				   TY.REG_NUMBER AS RN
			  FROM SHOWING_VALUES_21_Y ty
			/*quarters*/
			UNION ALL
....................................................
....................................................
....................................................
			UNION ALL
			SELECT TO_DATE (''''0112'''' || Tm.SHOWING_DATE, ''''ddmmyyyy'''') AS "DATE",
				   ''''декабрь '''' || Tm.SHOWING_DATE AS "SDATE",
				   NULL AS "YEAR",
				   12 AS "MONTH",
				   NULL AS "QUARTER",
				   ''''M'''' AS "TYPE",
				   tm.FACT_NI_12 AS "VALUE",
				   TM.REG_NUMBER AS RN
			  FROM SHOWING_VALUES_21_m tm) tyqm
	WHERE tyqm.TYPE = UPPER (''''' + @type + ''''')
		   AND RN IN (SELECT reg_number
						FROM showing_list_21
					   WHERE ident = ''''' + @ident + ''''')
	'')';
	INSERT INTO @t_IndicatorData (DATE,SDATE,YEAR,MONTH,QUARTER,TYPE,VALUE) EXEC (@t_sql);
	SELECT DATE,SDATE,YEAR,MONTH,QUARTER,TYPE,VALUE FROM @t_IndicatorData;
	RETURN;
END;

Выдает сообщение:
Сообщение 443, уровень 16, состояние 14, процедура F_POK_DATA, строка 203
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.
Сообщение 444, уровень 16, состояние 2, процедура F_POK_DATA, строка 204
Select statements included within a function cannot return data to a client.

Понимаю, что совместное использование 'INSERT EXEC'.... Но мне надо как-то выкрутиться...
Делаю процедуру....
И делаю тестовый ее вызов:
DECLARE @type varchar(1) = 'Q', @ident varchar(6) = '000697';
DECLARE @IndicatorData TABLE 
(
    DATE datetime,
    SDATE varchar(200),
    YEAR int,
    MONTH int,
    QUARTER int,
    TYPE varchar(1),
    VALUE float
);

INSERT INTO @IndicatorData (DATE,SDATE,YEAR,MONTH,QUARTER,TYPE,VALUE) EXEC P_IndicatorData @type,@ident;
SELECT DATE,SDATE,YEAR,MONTH,QUARTER,TYPE,VALUE FROM @IndicatorData;

Работает...
Если этот код запихнуть в функцию, опять выдаст сообщение, что нельзя использовать 'INSERT EXEC'...
Как сделать функцию?
Мне нужно чтобы другие ребята по простому, не заморачиваясь могли получить результат...
Прошу помощи...

К сообщению приложен файл. Размер - 26Kb
27 июн 12, 07:29    [12779865]     Ответить | Цитировать Сообщить модератору
 Re: Создание функции, возвращающее табличное значение  [new]
Владимир СА
Member

Откуда:
Сообщений: 7915
Да-с забыл указать:
SELECT @@version

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   
Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) 
on Windows NT 6.1 <X64> (Build 7600: ) 
27 июн 12, 07:42    [12779878]     Ответить | Цитировать Сообщить модератору
 Re: Создание функции, возвращающее табличное значение  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
CLR
27 июн 12, 08:04    [12779903]     Ответить | Цитировать Сообщить модератору
 Re: Создание функции, возвращающее табличное значение  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
В функциях допустимы следующие инструкции
...
Инструкции INSERT, UPDATE и DELETE, которые изменяют локальные табличные переменные.
Инструкции EXECUTE, вызывающие расширенные хранимые процедуры
...

почему сразу не делать OPENQUERY?
27 июн 12, 08:17    [12779926]     Ответить | Цитировать Сообщить модератору
 Re: Создание функции, возвращающее табличное значение  [new]
Владимир СА
Member

Откуда:
Сообщений: 7915
HandKot
В функциях допустимы следующие инструкции
...
Инструкции INSERT, UPDATE и DELETE, которые изменяют локальные табличные переменные.
Инструкции EXECUTE, вызывающие расширенные хранимые процедуры
...

почему сразу не делать OPENQUERY?
Где?
27 июн 12, 08:46    [12779983]     Ответить | Цитировать Сообщить модератору
 Re: Создание функции, возвращающее табличное значение  [new]
Владимир СА
Member

Откуда:
Сообщений: 7915
tpg
CLR
Надо рассмотреть конечно этот вариант...
Что-то я еще не использовал их...
В доке что-то не разобрался, где я должен прописать обращение к OPENQUERY ?
27 июн 12, 08:48    [12779990]     Ответить | Цитировать Сообщить модератору
 Re: Создание функции, возвращающее табличное значение  [new]
Glory
Member

Откуда:
Сообщений: 104751
Владимир СА
В доке что-то не разобрался, где я должен прописать обращение к OPENQUERY ?

Будьте проще

-- Execute several statements on the linked Oracle server.
EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', 7902) AT ORACLE;
GO
DECLARE @v INT; 
SET @v = 7902;
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', @v) AT ORACLE;
GO
27 июн 12, 11:17    [12780984]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить