question

michal avatar image
michal asked

How to create an indexed view using dynamic SQL

hi i want to write clustered index view. the problem is that the query i want to use for the view must be dynamic query. i know all the columns i will get from the query (=from the view), but i must execute it dynamicly because i dont know all of the columns of one of the tables i use for the query. this table is not part of the select clause, therefor i know exactly what will i get in the select clause of the view. how can i create such view? i searched in the net and havent find way to create 'dynamic view' please help me... Thanks, Michal

sql-server-2005viewdynamicindexed-view
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I don't think you can do what you're trying to do. If you post a sample table, data and the result you want we might be able to help.
0 Likes 0 ·
can you use Stored Procedure to do what you want ?
0 Likes 0 ·

1 Answer

· Write an Answer
graz avatar image
graz answered

A view can't contain dynamic SQL. Therefore an indexed view can't contain dynamic SQL.

I would consider building a table and using a batch job or triggers to populate it and keep it up to date.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.