SQL Server Performance Forum – Threads Archive
Wildcard Searches
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 AdvanceToni Chaffin
aka Toni
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
WHERE src_terrier.Areacode LIKE @chr_div @chr_div = ‘N%’
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
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
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
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
Thank you Twan. I shall have a go at these right now Kindest Regards Toni Chaffin
aka Toni
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
use the line set dateformat within your proc to force varchar to assume dmy, see BOL for more info cheers
Twan
]]>