function within view

I have a view built like this
CREATE VIEW XXX
AS
select * from XXX_Calculate()
the XXX_calculate() function is built like this
CREATE FUNCTION XXX_Calculate ()
RETURNS @.Result TABLE (XXXID bigint NOT NULL, XXX2ID bigint NOT NULL)
....
it contains cursors which insert values to the @.Result table.
The problem is whenever a user calls this view the function is beiing
executed again so the retrieval is slow...
Is there a hint to have it behave like normal view?
Thanx in advance.
Sorry about my poor English...P Platan:
At first i'm trying to to use cursors at all.
Because as you see it works very slow, i would offer you to think how to be
avoid usin cursor.
If your example is as well as your view', I dont see whay do you need view
at all. On many programs that use sql you can use SELECT * fron function().
I would offer you to use store procedure instead of view. because on store
procedure you can set the function result on one temporary table and use it
as you can in the store procedure. Also all other software who work with sql
server can use store procedure as well as view.
"P Platan" <pplat@.exnds.com> wrote in message
news:ulItixdQGHA.4536@.TK2MSFTNGP10.phx.gbl...
>I have a view built like this
> CREATE VIEW XXX
> AS
> select * from XXX_Calculate()
> the XXX_calculate() function is built like this
> CREATE FUNCTION XXX_Calculate ()
> RETURNS @.Result TABLE (XXXID bigint NOT NULL, XXX2ID bigint NOT NULL)
> ....
> it contains cursors which insert values to the @.Result table.
> The problem is whenever a user calls this view the function is beiing
> executed again so the retrieval is slow...
> Is there a hint to have it behave like normal view?
> Thanx in advance.
> Sorry about my poor English...
>|||I use view because it resides in another database from the one that the
function calls.
To be more specific
In the old datbase schema we had a basic table with 150 categories as fields
I the new implementation we want to normalize it and have them 'vertical'.
In order not to transfer lots of data to the other db and not load triggers
in the basic table which is accessed very heavily we created the view to the
new db which 'verticals' the categories-fields of the basic table.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uNX6wjeQGHA.5248@.TK2MSFTNGP09.phx.gbl...
>P Platan:
> At first i'm trying to to use cursors at all.
> Because as you see it works very slow, i would offer you to think how to
> be avoid usin cursor.
> If your example is as well as your view', I dont see whay do you need view
> at all. On many programs that use sql you can use SELECT * fron
> function().
> I would offer you to use store procedure instead of view. because on store
> procedure you can set the function result on one temporary table and use
> it as you can in the store procedure. Also all other software who work
> with sql server can use store procedure as well as view.
> "P Platan" <pplat@.exnds.com> wrote in message
> news:ulItixdQGHA.4536@.TK2MSFTNGP10.phx.gbl...
>