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.

  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'


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

    SELECT *
    FROM OPENQUERY(LinkedServername, 'EXEC Mydatabase.dbo.StoredProc')

    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.

  5. bradmcgehee New Member

    Great tip!

    Brad M. McGehee, MVP
  6. sqljunkie New Member


    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