Sql 7 stored proc problem

Results 1 to 2 of 2

Thread: Sql 7 stored proc problem

  1. #1
    Bob Rowson Guest

    Default Sql 7 stored proc problem

    Hello,<BR><BR>I&#039m looking for help with a problem that I have with a stored procedure in Sql 7. I have a table of business addresses (about one million records) that I want to search using three possible parameters: CompanyName; StreetName; TownName; Obviously, the parameters have to be wildcarded and equal to &#039all&#039 in the event that a parameter is not entered. Each of these fields is indexed and the table has a separate auto-incrementing primary key. If I perform the search using just the CompanyName and &#039all&#039 for the other parameters (like [a -z]% for example), the proc runs screamingly quick even allowing for the wildcards. Likewise, if I further restrict the search with Street and Town criteria. If, however, I repeat the same proc but with &#039all&#039 set for the CompanyName and Street and Town names entered, it can take up to a minute or so to return a recordset. The stored procedure I am using is:<BR><BR>CREATE PROCEDURE NameTownQry<BR><BR>@WhatCo varchar(50) =&#039[a -z]%&#039,<BR>@WhatStreet varchar(50)=&#039[a -z]%&#039,<BR>@WhatTown varchar(40)=&#039[a -z]%&#039<BR><BR>AS<BR><BR>SELECT Company, Department, Building, SubBuild, StreetNo, StrDepend, Street, Locality, POBox, Town, County, Postcode, Easting, Northing<BR>FROM Address<BR>WHERE (Company like @WhatCo) AND (Street like @WhatStreet) AND (Town like @WhatTown)<BR>ORDER BY Company<BR>return<BR><BR><BR>I realise that I could either forget about using a stored procedure and just create the sql on the fly depending upon the criteria submitted by the user, or I could set up several stored procedures to take care of every possible search criteria combination BUT this type of parameter query works in Access plus it works using &#039like *&#039 and it works quickly (if I try &#039like %&#039 with sql, I could make a cup of tea whilst I wait for a recordset back!).<BR><BR>So the real question is why Access and not Sql?<BR>And is there any way to ignore a stored proc parameter but without having to set a default value in lieu of no parameter?<BR><BR>Sorry this has wandered on a bit.

  2. #2
    Steve Lewis Guest

    Default RE: Sql 7 stored proc problem

    Bob<BR><BR>I think it&#039s because the select statement is ordering on company but not too sure why. Try adding Street and Town to the order statement. Or just search on Street and order by Street to see if my guess is correct. Plus, if you have SQL Profiler installed then this will show where all the processing time is being spent.

Posting Permissions

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