Is it possible to create view from stored procedure for ase 12.5?

[Former Member]

Suppose I have SP to return result set like:

create proc mysp

          ( @param varchar(30) )

     as

     begin

         //many logic here....

          select col1, col2, col3, ...

          from #mytable

     end

then I want to a view can get data from this sp.  As this sp include many sql, not only on select. also this sp has input parameter.

For this case, any solution to create a view from this sp on ase 12.5?

[former_member188958]

Not directly.

If simply creating a view with similar logic isn't possible, a way to do this would

be to create a loopback server entry for your server, and then create an rpc-based CIS proxy table on the procedure.

-bret

[Former Member]

Actually I have the solution with proxy table from sp with loopback server. but it has a performance problem. So I am looking for a solution to replace this proxy table.

I can create a physical table to hold the result from sp same as proxy table, but the difference is:

proxy table can call the sp automatically every time when you issue select from this table.

but with physical table solution, it is a empty table, like a place holder. So I want to the table have the data every time when use issue select from this table, then  I need to call the sp to fill up data in this table manually. Not sure how to fill data automatically when anyone acccess this physical table.

[former_member188958]

I believe the usual pattern used for this sort of circumstance is to have

the client create a #temp table, call the procedure (which is written to insert the results of the select into the #temp table), and then select the results from the #temp table.

This use of the #temp table keeps the results private to the session and current to the session's most recent execution of the procedure.

-bret

[Former Member]

I am not sure but do you pass different parameters every time user calls it. if yes, One way would be to get all the data if possible into a physical table by executing it. Do not apply any restrictions at this point. Then users can query it and can apply the restrictions. You will need to populate the table if data is being changed. If data changes are frequent, then do noyt even try this solution.

Thanks

[former_member185199]

we cant really give you a solution for this without a proper use case, are your data session based, are every client selects his "own" data from that "view" and so on and so on

what are the performace issues you have with the proxy loopback ? is the procedure perfoming much better when you call it directly?