CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-view
JonTravo
JonTravo

Reputation: 25

SQL Server stored procedure with views

I change some entries from a table with a stored procedure and this changes impact a view.

When is the view updating? When the stored procedure is working (after the specific statement) or when it is completed?

I'm using SQL Server 2019 (v15).

Upvotes: 0

Views: 533

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 12959

If you have done schema changes to underlying Tables:

It does not happen automatically. You have to specifically call sp_refreshsqlmodule to refresh the view

EXEC sp_refreshsqlmodule 'SchemaName.ViewName'

sp_refreshsqlmodule should be run when changes are made to the objects underlying the module that affect its definition. Otherwise, the module might produce unexpected results when it is queried or invoked. To refresh a view, you can use either sp_refreshsqlmodule or sp_refreshview with the same results.

You can also use sp_refreshview to refresh view for the underlying changes.

EXEC sp_refreshview N'SchemaName.ViewName'

If a view is not created with schemabinding, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried

It is generally best practice to create views with SCHEMA BINDING option, which will avoid schema changes to underlying tables. Benefits of Schemabinding

If you have done data changes to tables, not schema changes:

View is nothing but a stored SQL Statement. It is a virtual table. When you select from a view, the corresponding table query is being run at run time. View is not storing the data separately. So, no need to update or refresh the view.

Upvotes: 1

Related Questions