Re: How to use a an integer parameter to fetch parameter = "N" or all rows.

Article View: borland.public.interbase.sql

Article #849

Back to borland.public.interbase.sql Previous Next

#849

From: Wayne Niddery [TeamB]
Date: Wed, 30 May 2007 14:28

30 lines
1892 bytes

Mitch wrote:
> How can I easily query an integer column to get all the rows matching
> an integer or also allow all rows to be fetched? EG:
>
> Item.Project (integer)
> Item.Location (integer)
>
> (Case 1)
> Select * from Item where
> Project = :ProjectID and Location = :LocationID
>
> (Case 2)
> Select * from Item where
> Project = :ProjectID and Location = all locations

If you really cannot change the SQL to drop the location condition, then the 
only way is to test the parameter in the SQL as well, but doing this will 
mess up use of indexes and thus may result in poor performance - you'll have 
to test this:

Select * from Item where
(Project = :ProjectID or :ProjectID is null)
and
(Location = :LocationID or :LocationID is null)


-- 
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"It used to be that other people's achievements were considered an
inspiration, not a grievance."

Technical Details

Message-ID: <465d8a1c$1@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!g2news1.google.com!news2.google.com!news.glorb.com!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail

References: <465d29fa$1@newsgroups.borland.com>

Show Full Headers

Complete Article Headers

X-Google-Thread: 114a0e,d43d3e3c175ab47e
X-Google-Attributes: gid114a0e,public,usenet
X-Google-Language: ENGLISH,ASCII-7-bit
Reply-To: "Wayne Niddery [TeamB]" <wniddery@chaffaci.on.ca>
From: "Wayne Niddery [TeamB]" <wniddery@chaffaci.on.ca>
Newsgroups: borland.public.interbase.sql
References: <465d29fa$1@newsgroups.borland.com>
Subject: Re: How to use a an integer parameter to fetch parameter = "N" or all rows.
Date: Wed, 30 May 2007 10:28:45 -0400
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 69.17.170.228
Message-ID: <465d8a1c$1@newsgroups.borland.com>
X-Trace: newsgroups.borland.com 1180535324 69.17.170.228 (30 May 2007 07:28:44 -0700)
Path: arc1-ca.pugleaf.net!g2news1.google.com!news2.google.com!news.glorb.com!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail

Reply to Parent Message