How to execute stored procedure inside view in SQL server

Question

How to execute stored procedure inside view in SQL server

Answer 1

Hi @Reportboy2021 ,
You cannot call a stored proc from inside a view. It is not supported. However you can make views call other views, or table-valued user-defined functions.

For the latter you must make sure that you're using inline functions. Otherwise, any subsequent clauses like WHERE, GROUP BY and ORDER BY to will be performed on the dynamically produced resultset instead. Thus, you won't benefit from index searches and the likes. This may have a huge impact on performance.

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can't. It is not possible.

A view can only contain a SELECT statement.

Answer 3

What Tom says is not 100 % true. However, the solution is so completely flawed that I am not going to share it. Describe your real problem instead and we may be able to help you.

Answer 4

Hi，@Reportboy2021
The view (VIEW) virtual table is a section of SELECT statement of the query.Please refer to this Document for more details.
If you insist on excuting a stored procedure in the view,I have few immature ideas :
(1)Encapsulate the statement in the stored procedure into a function and then call it in the view.
(2)Use OPENROWSET like this:

Create VIEW vwTestViewName               
AS  
SELECT *  
FROM OPENROWSET( 'SQLNCLI',   --DBlink   
                 'DRIVER={SQL Server};SERVER=192.168.0.13;UID=sa;PWD=sa; Trusted_Connection=no',  
                 'SET FMTONLY OFF;SET NOCOUNT ON;EXEC PieroTest.dbo.SP_StoredProcName')   

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.