Does anyone know if it is possible to call a stored proc from a view? If so could you post an example?
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.
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.
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.
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.
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.
