I am relatively new to using SQL Server, so sorry if this is impossible or very easy and I missed the solution.
I am working on an enterprise SQL Server 2008 R2. Because of the size of the company, we do not want to create new Views as they need to be maintained. With that said, I need to use Views and not tables because of DB traffic issues.
HERE IS MY QUESTION:
Is it possible to Alter a view at run time; in this specific instance I want to change a constraint in the WHERE clause to include one more value in an IN() constraint. Specifically I want the modified SQL to go from WHERE column IN('A', 'B', 'C') to
WHERE column IN('A', 'B', 'C', 'L'). I do not want to permanently modify the View, I just want the View to change when it is run for this report. I need to do this in order to fix a dynamic report. I am sure that there is another way to accomplish this; however, a solution of this manner would also have many other uses, so I am curious if this is possible.
Feel free to tell me that it can be done.
Thanks for your ideas!!
Can it be done at runtime? Yes. Can it be done without permanently modifying the view? No.
But it is likely the wrong approach for many reasons. I will also say that your reasoning for implementing views is highly suspicious. A view is simply a pre-coded select statement and has no more and no less impact on database resources or "traffic" than the query which defines the view. Altering a view also requires certain privileges that an average user or application should not have. Lastly, you cannot temporarily alter a view - when altered it is permanent and affects all users.
If you describe what it is you are trying to do, someone might be able to provide alternatives. It would also help to understand the reasons for your 2 conflicting statements in the 2nd paragraph (don't want to create new views since views need to be maintained, use view because of DB traffic issues) - it seems to me that there may be some basic misunderstandings or assumptions that need to be corrected.
Right, I understand those fundamentals. We outsource a lot of out view creation and do not have permission to create views for Business reasons; these are beyond my control. However, I can see the source code to many Views. We have also been told that we should not use tables in our queries. Therefore, I need to use existing Views. Specifically, I would like to add an additional value to an IN() constraint. This would change the constraint as showed below:
IN('A', 'B', 'C') ---> IN('A', 'B', 'C', 'L')
I am wondering if this change can be made when the view runs and only for this one report, with the change being made from the report. This would be very easy if the constraint was removing a value. Obviously I could change the constraint from IN('A', 'B', 'C') to IN('A','B') very easily with out modifying the View. This would be done by writing a query against the view and in the WHERE clause adding the new IN('A', 'B') statement. However, I want to do the opposite and add a value...
if you need it for a report, then stored procedure or table valued function would be more appropriate. Both stored proc or tvf are available to be used as "parametrized views". Answer to question if view can be changed at runtime is yes, but it is not the preferred way to do it.
If you do not have permission to create new views, then you likely do not have permission to alter existing views. So your path doesn't seem feasible from the outset. The rest of your 1st paragraph description gives me chills since it sounds like a lot of rules that are shortsighted, misguided, or maybe misunderstood.
As for your 2nd paragraph, I already mentioned that you cannot "temporarily" alter a view. I will mention (but not recommend) that you can alter a view in a transaction and then rollback the transaction after you "finish" the report. That can have repercussions elsewhere - significant ones. TVFs might be a possible solution - but functions have limitations. And really, the fact that you cannot create new views leads me to believe that you cannot create functions either. The idea that one can "outsource ... view creation" boggles my mind.
Parameters for a SSRS report (2008 R2, for example): http://technet.microsoft.com/en-us/library/aa337432(v=SQL.105).aspx
Basically, the idea is to filter your view (output) during the input to your report creation (assumed to be SSRS, SQL Server Reporting Services)
Mark Tabladillo PhD (MVP, SAS Expert; MCT, MCITP, MCAD .NET) http://www.marktab.net