GoldenLine
Zaloguj się
Emil Studziński

Emil Studziński Inżynier
Oprogramowania

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Witam,
Chciałbym utworzyć funkcję, która zwróci mi zdefiniowany typ tabeli.

przykładowo:
stworzyłem odpowiedni typ tabeli:

CREATE TYPE dbo.tmpusers TABLE
(
userid int,
name nvarchar(100)
)

W odpowiedniej procedurze mam utworzony obiekt nowego typu:

DECLARE @a dbo.tmpusers

Teraz zamiast klasycznego dodawania do @a za pomocą inserta:

INSERT INTO @a (userid, name)
SELECT 1, 'test'

chciałbym stworzyć funkcję, która będzie pobierała 1 parametr (np int) i zwróci mi obiekt typu dbo.tmpusers

a'la:
CREATE FUNCTION dbo.fn_getUsers (@status int)
RETURNS dbo.tmpusers
as
BEGIN
declare @a dbo.tmpusers
INSERT INTO @a (userid, name)
SELECT 1, 'test'
return @a

END

Niestety to nie działa... Może ktoś wie, jak sobie z tym poradzić (tzn stworzyć funkcję, która zwróci zdefiniowany obiekt typu tabelarycznego)?
Link do wypowiedziLink
Wojciech Brochowicz

Wojciech Brochowicz programista

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Emil S.:
Może ktoś wie, jak sobie z tym poradzić (tzn stworzyć funkcję, która zwróci zdefiniowany obiekt typu tabelarycznego)?

Trochę leciwy przykład http://technet.microsoft.com/en-us/library/aa214485%28...
Link do wypowiedziLink
Emil Studziński

Emil Studziński Inżynier
Oprogramowania

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Trochę leciwy przykład http://technet.microsoft.com/en-us/library/aa214485%28...

Widziałem ten przykład i w podobny sposób rozwiązałem problem, tzn:

tworzę zmienną określonego typu tabelarycznego, później klasyczny insert

DECLARE @u dbo.tmpusers

INSERT INTO @u (userid, name)
SELECT * FROM dbo.fn_getuserlist(1)

Gdzie fn_getuserlist (@param int) returns TABLE

Później oczywiście jakaś procedura z @param dbo.tmpusers READONLY

Oczywiście działa, natomiast przy takim modelu niestety nie mam większej kontroli nad tym, co zostanie przekazane przez funkcję, np jeśli ktoś zmieni tabelę zwracaną przez fn, wtedy kaplica :)

Gdybym mógł zwracać parametr własnego typu (zamiast TABLE), wtedy mam z góry nałożoną strukturę i nie zachodzi ryzyko, że coś się posypie (czyli nie panuję nad tym, jaką strukturę zwróci fn_getUserList() ); w tym przypadku TABLE jest nazbyt ogólne

Trochę tak, jakbym w C# próbował przekazać obiekt typu object parsowany na int do zmiennej typu int - rosyjska ruletka :)
Link do wypowiedziLink
Damian L.

Damian L. Architekt IT

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Wydaje mi się, że się nie da.
http://msdn.microsoft.com/en-us/library/bb510489.aspx

Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
Link do wypowiedziLink
Sebastian O.

Sebastian O.

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Napisz jak chcesz tego użyć, bo może jest łatwiejszy sposób rozwiązania Twego problemu.
Link do wypowiedziLink
Emil Studziński

Emil Studziński Inżynier
Oprogramowania

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Sebastian O.:
Napisz jak chcesz tego użyć, bo może jest łatwiejszy sposób rozwiązania Twego problemu.


Czy funkcja może, oprócz typów systemowych (INT, NVARCHAR, TABLE, etc) zwracać zdefiniowane przez użytkownika typy (np utworzony typ tabelaryczny dbo.tmpusers).

Czyli
CREATE FUNCTION zwrocmojtyp()
RETURNS @ret mojtyptabelaryczny

Tak jak napisałem: można zabezpieczyć się poniekąd przed ingerencją w zwracany typ, jeśli w środku określimy sobie, jak w środku ma wyglądać nasza tabela, ale już przy RETURNS, jesli ma to być tabela, muszę podać jego dokładną strukturę ręcznie

Najlepiej zobrazuje to poniższy przykład:

--tworzymy typ
create type dbo.mojtyptab as table
(
id int, name nvarchar(50)
)
GO
--tworzymy funkcję zgodną ze strukturą naszego typu
create function dbo.fntabletest()
returns @a table
(
id int,
name nvarchar(50)
)
as
begin
insert into @a values (1,'test')
insert into @a values (2, 'test')
return
end
GO

--test: wszystko ok, mieliśmy farta
declare @demo dbo.mojtyptab
insert into @demo
select * from dbo.fntabletest()

select * from @demo
GO
--teraz zmiana procedury
alter function dbo.fntabletest()
returns @a table
(
id int,
name nvarchar(10),--zmiana długości kolumny!
age int --nowa kolumna
)
as
begin
insert into @a values (1,'test',10)
insert into @a values (2, 'test',20)
return
end
GO

--...i próbujemy zrobic to samo przy naszym niezmienionym typie
declare @demo2 dbo.mojtyptab
insert into @demo2
select * from dbo.fntabletest() --> błąd, nie zgadza się liczba kolumn

--sprzątamy po demo
drop function dbo.fntabletest
drop type dbo.mojtyptab
Link do wypowiedziLink
Emil Studziński

Emil Studziński Inżynier
Oprogramowania

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Damian L.:
Wydaje mi się, że się nie da.
http://msdn.microsoft.com/en-us/library/bb510489.aspx

Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

Jako parametr wejściowy i owszem, nawet wydaje się logiczne :) Natomiast właśnie dlatego zamiast procedury zawierającej parametr wyjściowy chcę stworzyć funkcję z odpowiednim wyjściem
Link do wypowiedziLink
Damian L.

Damian L. Architekt IT

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Emil S.:
Damian L.:
Wydaje mi się, że się nie da.
http://msdn.microsoft.com/en-us/library/bb510489.aspx

Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

Jako parametr wejściowy i owszem, nawet wydaje się logiczne :) Natomiast właśnie dlatego zamiast procedury zawierającej parametr wyjściowy chcę stworzyć funkcję z odpowiednim wyjściem
Emilu w funkcjach można używać typów zdefiniowanych przez użytkownika.
Jedyny wyjątek to ten, o którym napisałem. Typy tabelaryczne mogą być używane pod warunkiem, że używasz ich jako parametrów wejściowych tylko do odczytu. Musisz chyba rozwiązać problem inaczej tak jak zasugerował to Sebastian Olszewski :)Ten post został edytowany przez Autora dnia 15.11.13 o godzinie 22:38
Link do wypowiedziLink
Marek Kubiś

Marek Kubiś programista c#

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Emil S.:
Damian L.:
Wydaje mi się, że się nie da.
http://msdn.microsoft.com/en-us/library/bb510489.aspx

Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
Jako parametr wejściowy i owszem, nawet wydaje się logiczne :) Natomiast właśnie dlatego zamiast procedury zawierającej parametr wyjściowy chcę stworzyć funkcję z odpowiednim wyjściem
Ale może warto zajrzeć do podstaw. W dokumentacji MSDN wyraźnie jest wskazane, że "Syntaktycznie funkcję można odróżnić od procedury tym, iż wymusza ona zadeklarowanie typu zwracanego jako wyniku przetwarzanego zapytania w jej ciele." więc z definicji nie działa to tak jak założyłeś.

W tym kontekście może jednak warto przemyśleć uwagę Sebastiana, bo wydaje się, że dobrze podpowiada bo
"dobre praktyki programowania w T-SQL nakazują, aby wykorzystywać procedury do operacji zapisu, modyfikacji, usuwania rekordów z bazy danych (czyli akcji, które nie zwracają konkretnych wartości), zaś funkcji - do przeprowadzania operacji matematyczno-analitycznych takich jak: liczenie średniej, dodawanie kolumn typu INT, przeliczanie wartości itp."

(źródło: http://msdn.microsoft.com/pl-pl/library/encyklopedia-s...
Link do wypowiedziLink
Emil Studziński

Emil Studziński Inżynier
Oprogramowania

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Damian L.:
Emilu w funkcjach można używać typów zdefiniowanych przez użytkownika.
Jedyny wyjątek to ten, o którym napisałem. Typy tabelaryczne mogą być używane pod warunkiem, że używasz ich jako parametrów wejściowych tylko do odczytu. Musisz chyba rozwiązać problem inaczej tak jak zasugerował to Sebastian Olszewski :)

Przykład na stronie widziałem, ale niestety odnosi się do procedury. Z funkcją już kombinowałem chyba na wszystkie możliwe sposoby (zarówno budując funkcję a'la "scalar" i "table-valued").

przykład na szybko:

CREATE TYPE dbo.testmojtyptab as TABLE(kol1 int)

CREATE FUNCTION dbo.test()
returns dbo.testmojtyptab --READOLNY /*też próbowałem, wtedy błąd przy READONLY*/
as
begin
declare @a dbo.testmojtyptab
insert into @a
select 1
return @a
end
--zwraca Must declare the scalar variable "@a".
Link do wypowiedziLink
Emil Studziński

Emil Studziński Inżynier
Oprogramowania

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Marek K.:
Ale może warto zajrzeć do podstaw. W dokumentacji MSDN wyraźnie jest wskazane, że "Syntaktycznie funkcję można odróżnić od procedury tym, iż wymusza ona zadeklarowanie typu zwracanego jako wyniku przetwarzanego zapytania w jej ciele." więc z definicji nie działa to tak jak założyłeś.

Wiem i właśnie chcę zadeklarować zwracany własny typ tabelaryczny (a nie TABLE) na wyjściu funkcji.
W tym kontekście może jednak warto przemyśleć uwagę Sebastiana, bo wydaje się, że dobrze podpowiada bo
"dobre praktyki programowania w T-SQL nakazują, aby wykorzystywać procedury do operacji zapisu, modyfikacji, usuwania rekordów z bazy danych (czyli akcji, które nie zwracają konkretnych wartości), zaś funkcji - do przeprowadzania operacji matematyczno-analitycznych takich jak: liczenie średniej, dodawanie kolumn typu INT, przeliczanie wartości itp."

Dobre praktyki również znam, wiem do czego służy funkcja...natomiast skoro T-SQL oferuje funkcje zwracające TABLE-VALUED, to czemu nie mógłbym z tego skorzystać, ale z góry nakładając własny typ?

Czemu TABLE-VALUED FN są fajne i praktyczne? Jeśli musielibyśmy zbudować kilka różnych funkcji, które zwrócą różne wyniki (przykładowo jakieś złożone sumowanie, jakaś inna suma, jakiś status) przy użyciu tych samych danych (np 3 JOIN'y), a wierszy gdzie musielibyśmy użyć 3 funkcji jest >100'000, zamiast tego możemy za jednym zamachem zwrócić 3 różne wartości z odpowiednim użyciem CROSS APPLY.

I w tym przypadku potrzebuję swojego typu tabelarycznego nie do wyświetlenia danych, tylko dopiero do dalszego uzyskania właściwego wyniku (stale korzystając z tego samego, przefiltrowanego źródła danych).
Czyli:
1. Potrzebna funkcja, która zwróci tabelę i zapisze do zadeklarowanej zmiennej
2. Silnie rekomendowane zastąpienie (w funkcji) TABLE moim własnym typem tabelarycznym --> to jest to, co mi się do tej pory nie udało, więc na razie "jadę" na RETURNS TABLE
Link do wypowiedziLink

konto usunięte

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

http://stackoverflow.com/questions/2501324/can-t-sql-f...

http://stackoverflow.com/questions/3089553/sql-server-...Ten post został edytowany przez Autora dnia 18.11.13 o godzinie 14:33
Link do wypowiedziLink
Marek Kubiś

Marek Kubiś programista c#

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Emil S.:
Wiem i właśnie chcę zadeklarować zwracany własny typ tabelaryczny (a nie TABLE) na wyjściu funkcji.
Ale ponieważ chcesz aby twoja funkcja zwracała różne tabele, np: z różną liczbą kolumn, tzn. że ta funkcja musi być niedetrministyczna. Determinizm funkcji zawsze jest sprawdzany przez motor serwera bazy SQL i nie jest łatwo niedeterminizm wymusić. Wśród wymagań jest np: Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same. Wystarczy użyć jakiejś funkcji agregującej lub zwracającej string ("All of the aggregate and string built-in functions are deterministic"), a nic z twojego zamiaru nie wyjdzie bo to co deterministyczne w ciele funkcji wymusi jej determinizm..
Dobre praktyki również znam, wiem do czego służy funkcja...natomiast skoro T-SQL oferuje funkcje zwracające TABLE-VALUED, to czemu nie mógłbym z tego skorzystać, ale z góry nakładając własny typ?
Nie napisałem swojej uwagi aby sugerować, że ich nie znasz lecz aby zwrócić uwagę że przy tak zaawansowanych kombinacjach jakie uskuteczniasz, wg mnie warto stosować się do dobrych praktyk, a te wyraźnie wskazują m.in. na zadawanie elementarnych pytań, przypomnienie sobie definicji, nadanie znaczeń (np: o czym świadczy potrzeba zwracania tabel z różną liczbą kolumn). ;-)
1. Potrzebna funkcja, która zwróci tabelę i zapisze do zadeklarowanej zmiennej
2. Silnie rekomendowane zastąpienie (w funkcji) TABLE moim własnym typem tabelarycznym --> to jest to, co mi się do tej pory nie udało, więc na razie "jadę" na RETURNS TABLE
Może użycie typu sql_variant dla parametu wejściowego wystarczy, a jak nie to trzeba dokładnie przeanalizować pod kątem wymuszania determinizmu tego co w ciele funkcji (declare, select, insert, ..) aby dowiedzieć się czy w ogóle jest możliwe :-( to co chcesz. Przynajmniej na koniec będzie wiadomo dlaczego się nie da. ;-)

Wszyscy "naokoło" piszą "się nie da" więc to co powyżej to jedyne co nasunęło mi się aby zmierzyć się z problemem. ;-)
Link do wypowiedziLink
Emil Studziński

Emil Studziński Inżynier
Oprogramowania

Temat: Problem: Funkcja, a zwracany własny typ tabelaryczny MSSQL

Cóż, co robić, jak żyć? :) wszystkim dziękuję za pomoc w poszukiwaniach.

Wielka szkoda, że nie można zwracać własnych typów tab (a przynajmniej bez jakiegoś kombinowania na około).
Korzyści wg mnie byłoby sporo (oczywiście jeśli podejdziemy do takich bytów jak do "tablic", czegoś "pod ręką" zależnego od konkretnego wiersza).

Do tego ciekawym (acz ryzykownym) elementem byłoby tworzenie tabel tymczasowych, które jako jedną kolumnę posiadałyby właśnie nasz typ tabelaryczny - z takim rozszerzaniem można sobie poradzić oczywiście za pomocą XML'a, a wspomniana propozycja zapewne grubo wykracza poza ANSI SQL :) Uważam, że innowacja w postaci dodania array'a lub listy w określonych przypadkach byłaby fajna (np przechowywanie adresów e-mail w jednym miejscu, zamiast w osobnej relacyjnej tabeli), gdyby jeszcze posiadały odpowiednie metody (np getvalue(int index), Length; etc)... taki dodatkowy wymiar :)
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

[mssql] Problem z procedurą




Wyślij zaproszenie do
Anuluj