How to execute stored procedure inside view in SQL server

Question

DECLARE @Query nvarchar(MAX);
Set @Query = 
N' SELECT  ' 

• @Columns + 
  N' FROM TABLA';
  --SELECT @Query
  EXECUTE sp_executesql @Query

I would like to fill a view with the result of this query EXECUTE sp_executesql @Query

CREATE VIEW v_tabla

AS 

EXECUTE sp_executesql @Query

Answer 1

You cannot run stored procedures from a view. And even less something that is based on dynamic SQL.

A view is just like a table in the regard that it represents an entity with a fixed set of attributes, represented as columns.

A view is just a canned static query. There is nothing dynamic about it.

Answer 2

Try the following:
DECLARE @Columns nvarchar(MAX);

SET @Columns = '*' DECLARE @Query nvarchar(MAX);

SET @Query =

N' SELECT ' +

@Columns + N' FROM TABLA';

CREATE VIEW v_tabla AS EXECUTE sp_executesql @Query

Answer 3

Hi @David Beltran

As the saying goes, practice makes sense, I followed your example first to create a stored procedure, the stored procedure is to query an existing table, and then I created a view and executed the stored procedure in the view.

Unfortunately, it reported an error, 'Incorrect syntax near the keyword 'exec'. Not to mention using dynamic SQL.

So, I'm sorry to say that the results you want are almost impossible to achieve.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". 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 4

execute stored procedure inside view

That's not possible/supported/allowed, see CREATE VIEW (Transact-SQL)