Turn filter on/off - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Turn filter on/off

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-17 : 14:04:06

I'm still relatively new to while loops and get the general gist of them, but start to zone out when there's a lot of conditional statements within them.

I'm trying to figure out how I would conditionally turn a filter on or off depending on an input parameter.

So for example, say I had the following table (set it as a table variable for convenience, but it would be an actual table)

[CODE]
declare @vehicles table (vehicleid int, vehiclenumber int)
insert into @vehicles (vehicleid, vehiclenumber)
values (1,26), (2,28), (3,29), (4,30) [/CODE]

Now say I declared a variable to be used as a parameter:

declare @Filter varchar(3)

If a user wanted all records, they'd:

set @filter='All'

If they wanted a particular vehicle, it would be:

set @filter='3' for example (using varchar to allow for the 'all' input)

Ultimately, what I'd like to return is this:

If @filter = 'All'
Select VehicleId
From @Vehicles

else
if @filter='3'
Select VehicleId
From @Vehicles
Where @filter='3'

The output from the first select in the If statement would be:

VehicleId
26
28
29
30

while the output from the second statement would be:

VehicleId
3

Hopefully this makes sense!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-17 : 14:14:04

making a single filter parameter to filter over all columns is not a good idea. in your case you can simply make a filter called @vehicleID and make query like


Select VehicleId
From @Vehicles
Where (VehicleId=@filter or @filter = 'All')

this will make sure it filters vehicleid based on what value you pass in @vehicleid filter and returns everything if you pass All

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-17 : 14:19:15

Vis,

Ok...I was trying to figure out the logic for an application. Basically, one of my staff is trying to make a combo box where it either selects a specific value or all values depending on what the input is. If the user selects "all" the idea is that the where clause goes away. If it is a specific vehicle, then it would select that vehicle.

Were your comments about not being a good idea due to SQL Injection? Or is there another issue that you foresee with going the first path?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-17 : 14:32:44

yep exactly. if you want make it common filter you've use dynamic sql where sql injection can occur.otherwise you need a set of conditions separated by or depending on how many fields you've to filter that might not give you best performance.

Basically, one of my staff is trying to make a combo box where it either selects a specific value or all values depending on what the input is

Reading above i have a doubt. If you're using common filter will you populate all filter values for all fields in same dropdown? Thats not a good thing from user perspective too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-17 : 15:18:15

I'm not that worried about SQL Injection; it's a small application with limited access and nothing confidential or earth shattering. Though I know it's probably a good practice to always protect against it regardless of the value/sensitivity of the application.

As for the drop down, I would liken it to something like in Excel. How when you use "Create List" on a column, when you click the drop down, it will list all values individually, but it will also let you select "no filter" so you see all records. THe user would simply select the value they want, or select "all values". As it is now, the form they're accessing selects 1 vehicle at a time. But we wanted to also allow it to turn the filter off so to speak.

We're sort of getting to the application side of things rather than the SQL. I was hoping I could use this as an opportunity to grasp some of the conditional phrasing needed within T-SQL statements. But I realize that it's important to know what the end goal is.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-17 : 15:30:50

[code]Where (VehicleId=@filter or @filter = 'All')[/code]
^ is still the best way.

But, I'd pass an INT, since VehicleID is INT, and instead of passing 'All', I'd not bother to pass it at all, and assume if no filter is selected that they want all.
[code]Where (VehicleId=@filter or @filter IS NULL)[/code]

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-17 : 15:41:22

ok...wait for it...."DUH!!!!!!!!!!"

As is usually the case, my brain made this much more complicated than it needed to be.

[CODE]
declare @filter int
---set @filter=1 --//Do not set if null, set if parameter desired

declare @vehicles table (vehicleid int, vehiclenumber int)
insert into @vehicles(vehicleid, vehiclenumber)
values (1,26), (2,28), (3,29), (4,30)

Select VehicleId
From @Vehicles
Where (VehicleId=@filter or @filter IS NULL)

[/CODE]

I'd still be interested to see how I can place this into a conditional statement in an if else clause but I'll mess with that to see if I can figure it out.

Thank you both for your help

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-17 : 16:28:13

That (and Russell's and Visakh's) solutions will work, but be aware they are performance nightmares waiting to happen. Dynamic SQL one of the best solutions for this, and it's completely possible to make it 100% SQL-injection proof.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--
Gail Shaw
SQL Server MVP

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-17 : 16:59:42

Gail,

Great article! I'm not sure that I could reproduce that on my own without some help quite yet, but I hodge-podged your sample for this purpose to try to see how it pans out. It looks like you conditionally set the where clause based on the length of the @where variable and then specify the resulting query based on whether or not there is a parameter being passed.

Here's my attempt and it returns the desired results, but I'd like to make sure that I'm missing something by how I reworked it:

[CODE]
create table #vehicles (vehicleid int, vehiclenumber int)
insert into #vehicles(vehicleid, vehiclenumber)
values (1,26), (2,28), (3,29), (4,30)

Declare @VehicleId int
set @vehicleid=null

DECLARE @sSQL NVARCHAR(2000),
@Where NVARCHAR(1000) = ''
SET @sSQL = 'SELECT VehicleID from #vehicles '
IF @VehicleId is not null
SET @Where = @Where + 'VehicleId=@_VehicleId '

IF LEN(@Where) > 0
SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)+1)

EXEC sp_executesql @sSQL,N'@_VehicleId int',
@_VehicleId = @VehicleId

go

drop table #vehicles
[/CODE]

Thank you! This is very cool

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-17 : 17:01:44

I think if your table is small, visakh16 and russell solutions are working fine and the code is cleaner. If your table is large, you may need an index on column VehicleId, but with that where clause condition, the index is not working well. You will have terrible performance.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-17 : 17:03:30

Hey Namman, yes the table is very small. Unless we grow to an enormous transit authority, we'll probably never have more than 50 vehicles at a time hence around 50 records to work with.

A lot of this is purely for my own learning.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-17 : 17:51:50

If it's 50 or so rows, well everything's fast on 100 rows. It's also not a huge problem if you have only one of those conditions in the where clause

I bring this up because it's one of the most common performance problems I see in client's code (I'm a consultant specialising in performance tuning), and it costs them huge amounts of money, in lost productivity, lost business and in my consulting fees.

--
Gail Shaw
SQL Server MVP

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-17 : 17:54:56

Thanks Gail. I realize a lot of this is probably overkill for such a small data set. I'm just really trying to learn the concepts more than anything. Thanks for taking the time :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources