Paramerized queries -OK in Access, but not SQLS2k?

Question

Paramerized queries -OK in Access, but not SQLS2k?

alex campbell

Valued Member

Points: 66
More actions
February 3, 2004 at 3:32 am

#107251

I have an application where users can enter data into any (or all) of 6 search fields,
to produce a filtered query.
This works fine using my Access version(see code below),
but as SQLS2k cannot use "IIF", I tried to replace these bits with
"CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields
as these cannot be "wild-carded" in the same way as Access allows.
Can anyone suggest a way forward that does not involve coding all the
possible permutations of "SELECT" blocks driven perhaps by lots of nested "IF/THEN/ELSE"s?
Hoping you can help
Alex
PARAMETERS
CurrentType Text,
CurrentCategoryID Long,
CurrentProductID Long,
CurrentClientID Long,
CurrentContractID Long,
FromDate DateTime,
ToDate DateTime;
SELECT
tAudit.AuditID,
tAudit.ActionType,
tAudit.ClientID,
tClients.ContactCompanyName,
tAudit.ContractID,
tContracts.ClientRef,
tAudit.ProductID,
tProducts.ProductName,
tAudit.CategoryID,
tCategories.CategoryName,
tAudit.Acknowledged,
tAudit.ValueAmount,
tAudit.DateStamp
FROM (((tAudit
LEFT JOIN tCategories
ON tAudit.CategoryID = tCategories.CategoryID)
LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID)
LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID)
LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID
WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType]))
AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID]))
AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID]))
AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID]))
AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID]))
AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

You should be able to emulate:

tAudit.ClientID Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID])

with:

tAudit.ClientID = IsNull(@CurrentClientID, tAudit.ClientID)

and, for the text field, emulate:

tAudit.ActionType Like IIf(IsNull([CurrentType]),"*",[CurrentType])

with:

tAudit.ActionType Like IsNull(@CurrentType ,"%")

Cheers,
- Mark

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Still trying to figure it out, but I think, Mark, your solution does not account for the Access LIKE comparison, or am I blind?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

Frank's friend Erland has written a fine essay on this problem:

http://www.sommarskog.se/dyn-search.html

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Oops, really? Yes, memory weakness

Now, isn't that nice from Erland

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

alex campbell Valued Member Points: 66 More actions · Answer 5

Thanks everyone for replying so quickly, & especially to Mark whose solution worked fine & is exactly what I needed.

Alex