Dynamic view

Is it possible to create dynamic sql views in Sql Server 2005?

I have a requirement of having to change the Sql View depending on the user selection criteria.
Is it possible?

No but you can create a stored procedure with dynamic sql kludge

If you would change the view (with an alter view statement) what would happen when 2 users would hit the view at the same time (problem)

sp with dynamic sql is your best bet (probably including a temp table, I don't know your requirements so that's tough to answer)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks.

I cannot use stored procedure, any other option to programattically alter the view?|||What is your requirement that means that you have to "dynamically change the view"?|||

Krutika wrote:

Thanks.

I cannot use stored procedure, any other option to programattically alter the view?

No. Well, you can use CASE statements in your view if that helps.|||No, A "view" is always the same list of fields.

Also, if you create a view which says:

CREATE VIEW V_TABLEA AS
SELECT * FROM TABLEA

When you create the view, it caches the list of fields it is going to display. This means if you add or delete a field from TABLEA, these changes will not show in the view. You need to recreate or alter the view to get a new field list.

Your only option is a stored procedure.