Функция с произвольной таблицей на выходе

← →
BPK (2004-05-28 13:29) [0]

Есть MSSQL2000.
Есть хранимая процедура, возвращающая таблицу с переменным количеством полей. Каждое поле может быть любого типа (nvarchar, int, float, bit и т.п.).
Состав полей изменяется в зависимости от характера затребованной информации.

Есть программа, которая должна затребовать информацию, хранящуюся в БД, но эта программа может брать информацию только из таблиц. Соответственно, нужно сделать так, чтобы она работала через View.
Насколько я знаю, в view нельзя вставить вызов хранимой процедуры, а можно вставить вызов функции.

В связи с этим возникает проблема: как объявить функцию так, чтобы она могла выдавать на выходе произвольные таблицы?
Ведь объявление табличного результата функции выглядит примерно так:

RETURNS @ResTable TABLE (
F1 int NOT NULL ,
F2 int NULL ,
F3 int NULL ,
F4 int NULL ,
F5 float NULL ,
F6 varchar(20) NOT NULL ,
F7 datetime NULL ,

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

← →
Ломброзо © (2004-05-28 13:52) [1]

returns table?

← →
Ega23 © (2004-05-28 13:57) [2]

Есть программа, которая должна затребовать информацию, хранящуюся в БД, но эта программа может брать информацию только из таблиц. Соответственно, нужно сделать так, чтобы она работала через View.

А почему не через ХП? Где сколько угодно вызовов других ХП поставить можно?

← →
BPK (2004-05-28 14:06) [3]

>А почему не через ХП? Где сколько угодно вызовов других ХП поставить можно?

Потому, что программа не видит ничего, кроме таблиц и View. Даже функции не видит.

← →
BPK (2004-05-28 14:08) [4]

Если вы мне подскажете, как можно вставить вызов уже имеющейся и отлаженной ХП в функцию или View, я буду благодарен.

← →
BPK (2004-05-28 14:13) [5]

Если returns table, то функция должна быть однострочной, но в случае невозможности вызова в однострочной функции ХП, придётся дублировать весь код ХП в функцию. В этом случае придётся объявлять выходную таблицу, а это противоречит требованиям.

← →
Ega23 © (2004-05-28 14:15) [6]

Потому, что программа не видит ничего, кроме таблиц и View. Даже функции не видит.

Работать напрямую с таблицами - вредно.
И как это программа не видит ничего, кроме таблиц и View? Политика безопасности так настроена?

← →
ZrenBy © (2004-05-28 14:19) [7]

CREATE VIEW MyView AS SELECT a.* FROM OPENROWSET("SQLOLEDB","XSERVER";"user";"password", "exec pubs.dbo.byroyalty 100") AS a

и

SELECT * from MyView

← →
BPK (2004-05-28 14:20) [8]

>И как это программа не видит ничего, кроме таблиц и View? Политика безопасности так настроена?

Не знаю, с программистами не общался.
Кстати, эта программа называется OrCAD CIS (профессиональный пакет для разработки схем)

← →
Nikolay M. © (2004-05-28 14:22) [9]

> Есть программа, которая должна затребовать информацию, хранящуюся
> в БД, но эта программа может брать информацию только из
> таблиц.

Случайно не какой-нибудь Business object?

> вызов уже имеющейся и отлаженной ХП в функцию или View

Процедуру нельзя вызывать из вьюх и функций. Но функцию можно вызвать из процедур и функций.

← →
BPK (2004-05-31 12:58) [10]

===== Освежаем тему ======

← →
Ломброзо © (2004-05-31 14:19) [11]

Блин. Показать проще, чем объяснить.

create function test(@dummy int) returns table
as
return (select 1 as "aaa", 2 as "bbb", 3 as "ccc")

create view v_test as
select * from dbo.test(null)

select * from v_test

← →
BPK (2004-05-31 14:37) [12]

Всё дело в том, что содержимое ХП не сводится к select 1 as "aaa", 2 as "bbb", 3 as "ccc".

Оно выглядит так:
--Получить список компонентов для указанной группы
CREATE PROCEDURE EcoGetList
@TypeID as int
AS
declare @sql as nvarchar(4000),@ParName as varchar(255), @ValType as nvarchar(255), @CatFields as varchar(8000), @CatVisFields as varchar(8000),
@Visible as bit, @Ctrl as varchar(50), @DicQuery as varchar(2000), @DicTable as varchar(50)
select @CatFields=PRESENTFLDS, @CatVisFields=LISTFIELDS FROM EL_EL_DCOMPTYP WHERE [ID]=@TypeID and DELETED=0
set @sql="select [ID]=cast(bc.[id] as int)"
declare pdefs cursor for select pd.param_name,td.type_sign,pd.dicdscquery,pd.dictable,pd.ctrls from el_el_dparamdefs pd inner join el_el_dtypedefs td on td.[id]=pd.param_type
open pdefs
while 1=1 begin
fetch from pdefs into @ParName,@ValType, @DicQuery, @DicTable, @Ctrl
if @@FETCH_STATUS=-1 break
if @@FETCH_STATUS=-2 continue
exec @Visible=EcoLocFieldPresent @CatFields,@ParName
if @Visible=0 continue
exec @Visible=EcoLocFieldPresent @CatVisFields,@ParName
if @Visible=0 continue
if @Ctrl="DIR"
set @sql=@sql+
",(select cast("+@DicQuery+" as nvarchar) from el_el_bcomppars pa "+
"left join "+@DicTable+" dir on dir.[id]=cast(pa.sval as int) "+
"left join el_el_dparamdefs pd on pa.param_id=pd.[id] and pa.comp_id=bc.[id]
where pa.deleted=0 and pd.param_name="""+@ParName+""") as "+@ParName
else begin
if UPPER(@ValType)="NVARCHAR" set @ValType="NVARCHAR(4000)" --Иначе обрезает строку
set @sql=@sql+
",(select cast(pa.sval as "+@ValType+") from el_el_bcomppars pa "+
"left join el_el_dparamdefs pd on pa.param_id=pd.[id] and pa.comp_id=bc.[id]
where pa.deleted=0 and pd.param_name="""+@ParName+""") as "+@ParName
end
end
close pdefs
deallocate pdefs
set @sql=@sql+" from el_el_bcomps bc where bc.deleted=0 and bc.ptype="+cast(@TypeID as nvarchar)
exec (@sql)
GO

← →
Ломброзо © (2004-05-31 14:42) [13]

C чем связано ограничение "барть информацию только из таблиц"?

← →
BPK (2004-05-31 14:45) [14]

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

Когда я пытаюсь ввести следующее:

CREATE VIEW dbo.VIEW1
AS
SELECT * FROM OPENROWSET("SQLOLEDB","";"my_user";"_my_password", "exec MyDB.dbo.EcoGetList 25")

то получаю ошибку:

Error 7357: Could not process object "exec El_Kd.dbo.EcoGetList. The OLE DB provider "SQLOLEDB" indicates that the object has no columns.

← →
BPK (2004-05-31 14:48) [15]

>C чем связано ограничение "барть информацию только из таблиц"?

С тем, что программа умеет брать информацию только из таблиц и View.
ПРОГРАММА НАПИСАНА НЕ МНОЮ!!! OrCAD это профессиональный буржуйский пакет, который может брать информацию о компонентах из базы данных через ODBC, но только из таблиц.

Может поможет

CREATE VIEW MyView with VIEW_METADATA

Но я сразу говорю, что эта конструкция написана мной
ради прикола

ЗЫ. В QA работает

А вариант "в лоб" - наделать нормальных таблиц специально для OrCad`a, а изменения в них производить триггерами, навешанными на другие таблицы - не пойдёт?

← →
BPK (2004-05-31 15:21) [18]

А вариант "в лоб" - наделать нормальных таблиц специально для OrCad`a, а изменения в них производить триггерами, навешанными на другие таблицы - не пойдёт?

Нет. Система многопользовательская. Этим всё сказано.

"No enter, no exit, no future"
Я сдаюсь.

← →
BPK (2004-06-01 17:21) [20]

наделать нормальных таблиц специально для OrCad`a

Кстати, такой вариант не пройдёт, т.к. невозможно предугадать, сколько будет полей и какого типа.

Хотя

CREATE VIEW dbo.VIEW1
AS
SELECT * FROM OPENROWSET("SQLOLEDB","";"my_user";"_my_password", "exec MyDB.dbo.EcoGetList 25")

и выдаёт ошибку, но если вставить в ХП перед exec (@sql) жёсткий select * from ..., то выполнение проходит без ошибок, правда, выдаёт не результат динамического, а результат статического запроса.

Функция с произвольной таблицей на выходе Найти похожие ветки