February 4, 2009 at 1:14 pm
Pretty standard stuff really...Stored procedure resultset is used for a report.
the input parameters on the front end may be specified or not depending whether or not they want to filter by that column.
in the past i've done it like...
create proc sp_report @param1 varchar(100) = null, @param2 int = null, @param3 datetime = null as
begin
declare @criteria varchar(100)
--initialize criteria string
set @critieria = 'where 1=1'
--check for presence of input params
if @param1 is not null
set @critieria = @criteria = ' and column1 = ' + @param1
if @param2 is not null
set @critieria = @criteria = ' and column2 = ' + @param2
if @param3 is not null
set @critieria = @criteria = ' and column3 = ' + @param3
--then finally
exec ('select * from table' + @criteria)
end
...while that code works i was thinking there has to be a non-dynamic sql way. So now i have been using this alternative way
create proc sp_report @param1 varchar(100) = null, @param2 int = null, @param3 datetime = null as
begin
select *
from table
where
column1 = isnull(@param1, column1)
and column2 = isnull(@param2, column2)
and column3 = isnull(@param3, column3)
end
although this is simpler for me to write, it seems that the execution plan is pretty inefficient.
I'm curious if there is a "best practice" solution to this problem as this seems to be a common use of stored procedures.
thanks
raoh
February 4, 2009 at 1:19 pm
There are a couple of other options for this kind of thing.
One is to use Union statements.
select Col1, Col2
from dbo.MyTable
where Col1 = @Param1
Union
select Col1, Col2
from dbo.MyTable
where Col2 = @Param2;
Another is to use Or instead of IsNull:
select Col1, Col2
from dbo.MyTable
where
(Col1 = @Param1
or
@Param1 is null)
and
(Col2 = @Param2
or
@Param2 is null);
In tests I've done on this, the dynamic version or the Union version has generally performed the best.
The main advantage of the Union version is that it doesn't open up any injection vectors.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 4, 2009 at 1:34 pm
Ok! I guess that the union would gradually degrade depending on how many parameters i'd have to string together?
I guess this one of those cases where dynamic sql is better.
February 4, 2009 at 1:42 pm
[font="Verdana"]Just a reminder: if you know that the lists (record-sets, sets, terminology of your choice) that you are combining don't have values in common, use union all, not union to avoid the additional overhead that union has. It's a good habit to get into using the union all form.[/font]
February 4, 2009 at 1:49 pm
The performance of the union version depends almost completely on what indexes you have on the table and how well the individual queries hit those. Secondarily, it can get slowed down by the dedupe function in Union, depending on the number of rows it's working on in the final select.
More often than not, the dynamic SQL version is going to be the best performer on this kind of thing. These other solutions won't be as fast, but they also don't have the security issues that dynamic SQL can have. It's a trade-off, as so many things are.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 4, 2009 at 1:53 pm
I tried your isnull method on a table I have on my server, and when I pass in all NULL parameters, I get no rows returned. I would think that you'd want all the rows returned in this case, or am I misunderstanding what you want. Do you want null to be a valid parameter for comparison against, or is a null parameter an indication that you don't want that parameter considered?
Personally, I don't think the dynamic sql is way to go, myself. It opens you up to injection attacks, for one, and for another, the query in the SP never gets optimized.
I personally use the:
Where field = @param or @param is null
all the time. It's not the absolute most efficient way, but it works pretty well in 99% of the cases I've tried it in. Assuming it produces the results you want ... I'd at least give that way a shot 😉
February 4, 2009 at 1:55 pm
raohtheconquerer (2/4/2009)
Ok! I guess that the union would gradually degrade depending on how many parameters i'd have to string together?
[font="Verdana"]Not necessarily. It depends on your hardware and the version/edition of SQL Server you are running, but SQL Server may parallelise the parts of the query that are unioned together. And it's more likely to do so if you use union all.
As a simple experiment, write 10 little simple select statements combined with a union/union all and look at the estimated query plan. You may well see that SQL Server decides to run some of the queries in parallel.
[/font]
February 4, 2009 at 1:59 pm
yeah that is correct. i kinda short handed it for ease of me posting. but really it looks more like this.
since equals(=) won't work on nulls...
isnull( column1, '') = isnull( @param1, isnull(column1,'') )....
but it seems that this way
(column1 = @param1 or @parm1 is null) ....
just makes more sense. thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply