Can you or can you not pass parameters to a view?
Well, the short answer is no. A view is seen by the database (and should be considered by us) as a table.
I’ve come across this question from time to time. The requirement is usually to reduce or restrict a dataset for a third-party. Well, as mentioned above, technically passing a parameter to a view is not possible, but there are other ways to achieve the required outcome either way:
- Use conditions in the “Where” section over a flat out View that holds all the data.
Even though this seems like a performance wise hazard or a very inefficient way of performing a query, modern databases handle these scenarios very well, and it can come very close to actually having the filters built into the view itself. This approach might not work best if you want to restrict the data rather then reduce the data from the dataset as the whole dataset is exposed to the one running the query. - Pass the parameters over a table, and filter with inner join and conditions inside the view.
This method can be both very efficient and rather secure way of selecting only the designated data from our view, but it does require another step before the select from the view can be done. An insert/update command needs to happen right before the select from the view to pass the required params. Some third-party systems can only do one query per action, and won’t be able to work this way. Another issue that can arise is that the view can show results that are not updated / or not supposed to be selected, but this can be solved by adding conditions on datetime or triggers. Use this method carefully. - Don’t use a view.
If passing parameters is a fundamental requirement of what you’re trying to achieve, maybe you’re better off with writing a Stored Procedure. Stored Procedures (or functions) are much more versatile and might be the way to go, but using them is not as easy a selecting from a table, and as mentioned before, might be problematic for the one that consumes the data.
Choosing the right way to go has more to do with the overall design of what is needed and the constraints that we’re facing. In this post, I wanted to show some of the ways that I know to overcome that technical issue.