SQL While loop with substring to get the first match

Question

0.00/5 (No votes)

See more:

Hi There,

I need some help to get a result back from sql. I have a 10 digit value that I am using in a while loop and want it to bring back the first correct match from the tables I am selecting from. this code is bringing back the correct data but it displays multiple items at the same time.

SQL

ALTER proc data_ee_test_fk
            as
            DECLARE @cli varchar(10)
            SET @cli = '123456789'
            WHILE len(@cli) > 0
            begin
            if exists(
            select distinct * from data_ee_dials
            where substring(dialprefix,1,Len(@cli)) = @cli)
            SELECT DISTINCT * from Data_EE_Codes a left join
            dbo.Data_EE_Dials b on a.CallType = B.ChargeCategory
            where substring(dialprefix,1,Len(@cli)) = @cli

            --select min( dialprefix1 ) from data_ee_dials  where substring(dialprefix,1,Len(@cli)) = @cli
            SET @cli = left(@cli,LEN(@cli)-1)
            print @cli
            end

without the second select it loops forever.

Please help :)

Posted 23-Aug-13 2:43am

Kats2512

Add a Solution

Comments

Zoltán Zörgő 23-Aug-13 8:50am

I didn't get exactly your intentions. Could you please give an example for the table, and the result you want to get.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

cigwork · Answer 1 · 2013-08-23T10:50:00

You don't make it clear but I assume that the problem is that you are starting with a 10 character candidate prefix and if nothing matches you need to widen the search by making the search prefix less specific. Having found a prefix you then want to find the first code that uses that prefix. If this isn't what you're trying to achieve please rewrite the question.

SQL

declare @done      bit
set     @done      = 0
declare @searchCLI varchar(10)
set     @searchCLI = '123456789'

-- Start narrow and then broaden the search until we find
-- a known CLI or give up.
while @done=0 and len(@searchCLI) > 0

  if exists(select dialprefix from Data_EE_Dials where dialprefix like @searchCLI)
  begin
    set @done = 1
  end

  if @done = 0
  begin
    set @searchCLI = substring(@searchCLI,1,len(@searchCLI)-1)
  end

end

-- We've either got a known CLI or a zero length string.
-- Return the first matching record if there is one.
select top 1 *
  from Data_EE_Codes as Codes
    inner join Data_EE_Dials as Dials
          on   Dials.ChargeCategory = Codes.CallType
  where dials.dialprefix like @searchCLI