2

I need to search a table for multiple phone numbers that are from an external source. Because the formatting in the table is not strict (1234567 or 123-4567), I used a wildcard and created a temp table to house the numbers.

I then have a while loop check for these individually through the database and put them in a third table.

I have read that cursor type commands are not efficient and there are generally better ways to solve such problems in SQL Server but my SQL knowledge is minimal at best and I don't even know where to start.

The query runs as is and returns exactly what I want it to, but when I ramp up the number of records, it increases the execution time linearly which is not sustainable if I want to search for say 1000 phone numbers.

Below is a snippet of my while code. How can I replace this with something more efficient? Please let me know if I should include additional information.

while @count_ < 77
Begin
    Select @record = (select number 
                      from @phoneNumbers
                      where Id_ = @count_)

    INSERT @updatesFinal
        SELECT 
            updates.[rowno], cust.no_, [empl],
            [emplbr], [time_], [super], [column_],
            [tablename], [urowno], [entered],
            [new], [old], [seqno_updatesix]
        FROM 
            [db1].[dbo].[updates]
        INNER JOIN
            db1.dbo.addr ON db1.rowno = updates.urowno
        INNER JOIN
            db1.dbo.cust ON cust.rowno = addr.rowno_custaddr_cust
        WHERE 
            updates.column_ LIKE ('phone%') 
            AND old LIKE @record

    SET @count_ = @count_ + 1
END
ChrisM
  • 1,576
  • 6
  • 18
  • 29
christodorov
  • 111
  • 1
  • 1
  • 7
  • from you code it looks like you want to find out the tables that have column named like phone%? but you said you what you want is phone numbers, i m so confused – Raffaello.D.Huke Jan 26 '16 at 02:12
  • 3
    Please include the schema's of the tables you are querying. Also, some sample data and the desired results would make it much easier to help devise a better algorithm. – erdomke Jan 26 '16 at 02:25
  • 1
    Read some introductions to SQL. There are many textbooks online. See [this](http://stackoverflow.com/a/32821740/3404097) recent answer and [this one](http://stackoverflow.com/a/33952141/3404097). – philipxy Jan 26 '16 at 05:10
  • 1
    Scrap everything except the INSERT statement, and then replace `WHERE updates.column_ like ('phone%') and old like @record` with `INNER JOIN @phoneNumbers AS numbers ON numbers.Id_ between 0 and 76 WHERE updates.column_ like ('phone%')` – Pieter Geerkens Jan 26 '16 at 05:43
  • 1
    @PieterGeerkens Thanks Pieter. I didn't realize that this could be done with an inner join. Your suggested solution is very close to what I used shortly after I posted this and it is nearly instant. I will post what I ended up with when I have a few minutes. Cheers! – christodorov Feb 02 '16 at 20:46

0 Answers0