SQL Server Performance

Stored Proc / View

Discussion in 'General Developer Questions' started by sqljunkie, Jun 24, 2003.

  1. sqljunkie New Member

    Does anyone know if it is possible to call a stored proc from a view? If so could you post an example?
  2. bambola New Member

    It is not possible to call a stored procedure from a view. It is sometimes possible to call a function from a view, depends on the function.

    Bambola.
  3. vbkenya New Member

    You can call a stored procedure within a view which utilizes an OPENQUERY statement. The stored procedure must of course return columns otherwise it will not be seen as a rowset.

    I created a 'fake' linked server to use for the OPENQUERY statement. This linked server points to the same instance as the one it was being created on (self-linkage) like this:


    EXEC sp_addlinkedserver @server='LinkedServerName', @srvproduct='',
    @provider='SQLOLEDB', @datasrc='MyInstance'


    GO


    I then created a view which uses the linked server and invokes one of the stored procedures:


    CREATE View XYZ
    AS
    SELECT *
    FROM OPENQUERY(LinkedServername, 'EXEC Mydatabase.dbo.StoredProc')
    GO


    I can't say that the linked server business will not affect the overall performance of queries utilizing this view.


    Nathan H.O.
  4. bambola New Member

    I didn't know that. You learn something new every day.

    Bambola.
  5. bradmcgehee New Member

    Great tip!

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  6. sqljunkie New Member

    Nathan,

    Thanks for your tip!
  7. vbkenya New Member

    It is said that "Necessity is the mother of invention". But I would wonder why Microsoft doesn't support the EXEC statement directly in the CREATE VIEW definition. It is definately not just a performance issue.

    Using this OPENQUERY method gives me the creeps though.

    sqljunkie: Why would you want to call a stored procedure within a view ?

    Nathan H.O.
  8. sqljunkie New Member

    Actually I don't really want to call a stored proc within a view.

    Someone asked me if it was possible and I could not think of a way so I posted the question among the experts in this forum.

    I was hoping I could go with Bambola's original reply because it seems like a very awkward position to be in. I think we'll try to redesign this scenario before we consider this a viable solution.

    Thanks again for your help.
  9. vbkenya New Member

    quote:Originally posted by sqljunkie
    [br......I think we'll try to redesign this scenario before we consider this a viable solution.......

    Very reasonable decision.

    Nathan H.O.

Share This Page