x

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.

more ▼

asked Nov 02 at 12:33 PM in Default

avatar image

raybales
1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Nov 02 at 12:42 PM

avatar image

RadimBaca
0 1

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.

Nov 02 at 03:35 PM raybales
(comments are locked)
10|1200 characters needed characters left

@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:

 -- 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

more ▼

answered Nov 03 at 11:03 AM

avatar image

DenisT
3.5k 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x24

asked: Nov 02 at 12:33 PM

Seen: 29 times

Last Updated: Nov 03 at 11:03 AM

Copyright 2017 Redgate Software. Privacy Policy