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.
