SQL Server Performance

Wildcard Searches

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Toni, May 13, 2006.

  1. Toni New Member

    I have a number of functions that require the input of parameters in order to ultimatly create a report under Reporting Services by making use of a Stored Procedure.

    All the functions etc work as does the stored procedure, but it only works if I specify data that I know exists e.g.

    DECLARE @return_value int

    EXEC @return_value = [dbo].[spWTRalldatareportsummary]

    @dt_src_date = N'04/28/2006',

    @chr_div = N'NE',

    @vch_portfolio_no = 3,

    @vch_prop_cat = N'core'

    SELECT 'Return Value' = @return_value

    GO

    How can I set this so that it will wild card the value. For example rather than having to specify

    @chr_div = N'NE', I could specify something like

    @chr_div = N *, so it would show both NE and SW values in the result set.

    Anybody point me in a direction here. I have tried % but that does not seem to work, I get a

    Msg 102, Level 15, State 1, Line 7

    Incorrect syntax near '%'.

    Thank in Advance


    Toni Chaffin
    aka Toni
  2. Toni New Member

    Currently my WHERE statements look like this;

    FROM src_terrier INNER JOIN
    src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
    src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
    src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no

    WHERE (src_terrier.datadate = @dt_src_date) AND
    (src_terrier.Areacode = @chr_div) AND
    (src_centre_list.Portfolio_no = @vch_portfolio_no) AND
    (src_centre_list.propcat = @vch_prop_cat)

    I have tried 'Like' in the following context

    Like (src_terrier.Areacode = @chr_div)

    and also (Like src_terrier.Areacode = @chr_div)

    Both of which errored when I tried to Parse the SQL and said it did not like the Like part

    Regards



    Toni Chaffin
    aka Toni
  3. joechang New Member

    WHERE src_terrier.Areacode LIKE @chr_div

    @chr_div = 'N%'
  4. Toni New Member

    Thanks for this. I have found though that seems to work OK on text fields great, but if I leave the INT parameter blank it does not like it.

    Is there a special character to use for INT fields?

    Regards


    Toni Chaffin
    aka Toni
  5. Twan New Member

    Hi ya,

    int fields don't support wildcard searches...

    what some people do is pass in a null, and then in the query have

    and (@field is null or col = @field)

    Cheers
    Twan
  6. Toni New Member

    Thanks Twan

    How would I implement that is my example above? Please excuse my slow updake on this, this is not my area at all, very new to querying in SQL, Access boy normally.

    Could I convert the value to a varchar for the purposes of the search?

    Regards


    Toni Chaffin
    aka Talisa
  7. Twan New Member

    Hi ya,

    the solution you choose will depend on the number of rows in the table and the difference in performance between the possible solutions. converting the value to varchar (and then also converting the int column to varchar) will limit the use of any indexes. Whether this is important or not will depend on the size of the tables involved.

    assuming @vch_portfolio_no is the column that is actually an integer, then I would either use


    FROM src_terrier INNER JOIN
    src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
    src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
    src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no

    WHERE (src_terrier.datadate = @dt_src_date) AND
    (src_terrier.Areacode = @chr_div) AND
    (@vch_portfolio_no is null or src_centre_list.Portfolio_no = @vch_portfolio_no) AND
    (src_centre_list.propcat = @vch_prop_cat)


    or if there are loads of rows then I'd use


    if @vch_portfolio_no is null then
    ...
    FROM src_terrier INNER JOIN
    src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
    src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
    src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no

    WHERE (src_terrier.datadate = @dt_src_date) AND
    (src_terrier.Areacode = @chr_div) AND
    (src_centre_list.propcat = @vch_prop_cat)
    ...
    else
    ...
    FROM src_terrier INNER JOIN
    src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
    src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
    src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no

    WHERE (src_terrier.datadate = @dt_src_date) AND
    (src_terrier.Areacode = @chr_div) AND
    (src_centre_list.Portfolio_no = @vch_portfolio_no) AND
    (src_centre_list.propcat = @vch_prop_cat)
    ...


    Cheers
    Twan
  8. Toni New Member

    Thank you Twan. I shall have a go at these right now

    Kindest Regards



    Toni Chaffin
    aka Toni
  9. Toni New Member

    Hi Twan, that seems to work fine. Just out of curiousity, you would not happen to know how I change the format that the user can enter the date. At the moment it is mm/dd/yyyy and I want it to be dd/mm/yyyy.

    It is for this line:-

    WHERE(src_terrier.datadate = @dt_src_date) AND ...

    Kindest Regards and thank you so mych for giving me the detailed description on the wildcard Null statement


    Toni Chaffin
    aka Toni
  10. Twan New Member

    use the line set dateformat within your proc to force varchar to assume dmy, see BOL for more info

    cheers
    Twan

Share This Page