question

raybales avatar image
raybales 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.
filter
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RadimBaca avatar image
RadimBaca answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, this will work. Many thanks. My one remaining question - in my experience views tend to slow query performance. If true, is there a more optimal solution? Thanks again.
0 Likes 0 ·
DenisT avatar image
DenisT answered
@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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.