Hi, I am a newbie to T-SQL. I used to use Foxpro SQL (much easier in some cases). Anyway, I work with multiple programmers in different departments and we all need to apply a common filter to filter out one of our business divisions.
It's a simple straight forward filter like division_number not in (2,5,10). The problem is that the filter changes monthly and this is not well communicated across different departments. I'd like to set up a stored ?function? (let's name it "filter_divs" centrally in our common DB so that one person can update it then it can be used/applied by anyone by using "filter_divs" function. Many thanks in advance.
I believe that view is appropriate for such think. Simply create a view like this:
Everyone can use such view easily like this:
@raybales The view is a good approach but I don't understand how it might help with -- "The problem is that the filter changes monthly and this is not well communicated across different departments." Say the next month the values would change... now you need to remember what was done a month ago, find the view, update with the new values, etc. I would create a stored procedure and a Table-Valued Parameter. This way the stored procedure will be able to dynamically handle the monthly changing parameters. Here is a simple test:
This is how one would run it:
Say the next month these values need to be changed, all that needs to be done is to change the INSERT INTO with the new values
Please note that you should have an index on tt.col1 (in my case!)
answered Nov 03, 2017 at 11:03 AM