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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Best practices using multiple Inputs (Optional)

Author  Topic 

Dave_Fergy
Starting Member

5 Posts

Posted - 2006-11-03 : 11:30:20
I have a report in which the user can input information into one or both fields and then the SQL will return information based on this input. I have created the SQL and made three different versions using the if statment to run one of the three versions based on the users input. I beleive that there should be a better way to execute this. Any input would be appreciated.

Currently I am doing the following:

Declare X char(5), Y char(10)

IF (not X = '') and (Y = '')
begin
version 1 SQL
where columnX = X
end

IF (X = '') and (not Y = '')
begin
version 2 SQL
where columnY = Y
end

IF (not X = '') and (not Y = '')
begin
version 3 SQL
where columnX = X
and columnY = Y
end

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-03 : 12:26:09
Are you using a stored proc? If so, do something like

create proc dbo.Dave_Report
@x char(5) = null,
@y char(10) = null
as
set no count on
select
<column list>
from
<table>
where
columnX = isnull(@x, columnX) and
columnY = isnull(@y, columnY) and
--what is your business rule here? I'm going to assume that
--no X and no Y gives you no results
(@x is not null or
@y is not null)
--leaving out this predicate will result in all rows returned
--when there is no X and no Y


exec dbo.Dave_Report @x ='string'
exec dbo.Dave_Report @y = 'longerstrg'
exec dbo.Dave_Report @x = 'string', @y = 'longerstrg'

Jay White
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-11-03 : 12:32:41
Check if this style performs better (which I think I've read somewhere ... but!)

WHERE (@x IS NULL OR columnX = @x)
AND (@y IS NULL OR columnY = @Y)
...

Whether checking performance again when columnX and/or columnY is indexed

EDIT: Fixed typos

Kristen
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-03 : 12:44:05
Kristen, I think you are missing some predicate to handle the cases where @x and/or @y are NOT NULL...

In my experience, so long as columnX stands on it's own on the left side of a predicate the optimizer will take advantage of any indexes it can.


Jay White
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-11-03 : 13:57:41
Sorry Jay, finger trouble I've fixed the typos in my previous post

I was looking for the Optimiser to ignore a step when @parameter is NULL

columnX = isnull(@x, columnX) requires that ColumnX is compared to itself under certain circumstances. That calculation might induce a SCAN (or even [when @parameter IS NULL] an INDEX traversal selecting all rows, except where it Barfs on rows where columnX IS NULL), whereas what I was trying to achieve was to get that statement optimised to "TRUE" so that there is no effort, and the next statement can then be examined to see if there is any "work to do"

But I'm hypothesizing rather than actually knowing, or having tested!, that this method actually performs well ...

Kristen
Go to Top of Page
   

- Advertisement -