Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Does this SQL call make you cringe?

    Hello -- This probably doesn't belong here, but here I am. I know that Office products to not necessarily include MSSQL Server, but here is a call to my server from an old, reliable report writer that I am using. Basically, I set up some hard coded prompts that the user is required to fill, which in this case I call "WholeClientorStartNumswPerct_orX."

    I want to offer the user the opportunity to enter the entire 6-digit client name, any amount of digits (followed by the percent), or "X", which I translate to a "%", which the code seems to be okay with. Actually, I could leave the "X" off entirely, because I find if I merely enter a "%" directly, it gets everything, so I'll probably change the user prompt and the below CASE statement before we even get to the "SELECT" statement, can just be discarded. However, I was curious, and the below works like a charm, but it *is* running a little slowly. So, do you think this sort of SQL is bad bad bad?

    DECLARE @ClientBit varchar(6)
    set @ClientBit = case upper(:WholeClientorStartNumswPerct_orX)
    when 'X' then "%" -- get any and all clients
    else :WholeClientorStartNumswPerct_orX -- get whatever else the user has entered
    end

    SELECT .... FROM ..... WHERE ...
    Then, in the 'Where' clause, I limit it to "Client like @ClientBit" which could be 123456, 1234%, or %.

    I do do stored procedures, so I could go that route, but for now.. just wanted to get any SQL-savvy people's take on it.

    thx
    Pat

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does this SQL call make you cringe?

    Nothing about your SQL statement seems like it should cause any performance issues. I'm not familiar with the notation where you plugged in your parameter - I assume that's specific to the calling application.

    The only thing I would recommend checking is the indexing of the field you reference in the WHERE clause. Just make sure that you've got the appropriate index and that it doesn't have like 1,000,000 rows. That *could* slow down the query a bit!

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does this SQL call make you cringe?

    Thanks, Mark.
    Yes, there is an index on that field, and that seems to be working pretty fast, but -- I have another field that I am using and I am timing out on that. So, I second your sentiment about indexing!
    Thanks for responding,
    Pat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •