Post reply

Paramerized queries -OK in Access, but not SQLS2k?

  • 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]));

  • February 3, 2004 at 4:04 am

    #492587

    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

  • February 3, 2004 at 4:30 am

    #492593

    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]

  • February 3, 2004 at 5:37 am

    #492596

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

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



    --Jonathan

  • February 3, 2004 at 6:00 am

    #492598

    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]

  • February 3, 2004 at 10:24 am

    #492648

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

    Alex

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

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