asked
Set up ?Function? for filtering
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: create view v_division_filtered as select d.* from division where division_number not in (2,5,10) Everyone can use such view easily like this: select * from v_division_filtered
@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][1]. This way the stored procedure will be able to dynamically handle the monthly changing parameters. Here is a simple test: -- create the test table and insert 15 rows IF OBJECT_ID('test_table') IS NOT NULL DROP TABLE dbo.test_table; CREATE TABLE dbo.test_table ( col1 INT NOT NULL IDENTITY(1,1), col2 DATETIME DEFAULT GETDATE(), col3 UNIQUEIDENTIFIER DEFAULT NEWID() ); -- run by itself due to GO 15 INSERT INTO dbo.test_table DEFAULT VALUES; GO 15 -- create Table-Valued parameter type CREATE TYPE dbo.IntTVP AS TABLE ( [value] INT NOT NULL ); GO -- create stored procedure CREATE PROCEDURE dbo.get_ints (@ints dbo.IntTVP READONLY) AS BEGIN IF NOT EXISTS (SELECT * FROM @ints) BEGIN RAISERROR('No values have been passed',10,1); RETURN; END SELECT tt.col1 , tt.col2 , tt.col3 FROM dbo.test_table AS tt WHERE NOT EXISTS (SELECT * FROM @ints AS i WHERE i.[value] = tt.col1); END GO This is how one would run it: DECLARE @p dbo.IntTVP; INSERT INTO @p ( value ) VALUES (2),(5),(10); EXEC dbo.get_ints @ints = @p; 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 INSERT INTO @p ( value ) VALUES (2),(5),(10); Please note that you should have an index on tt.col1 (in my case!) HTH, Denis [1]:
https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine
19 People are following this question.
