Problem in generating automaric code using storedprocedure

• • Reply

  13 years ago

  by windshadow2005

  Rajesh Kumar

  , India

  Joined 16 years ago

  Hi,

  I am generating a code in sql server 2005 storedprocedure. Which will be consider first 3 letter of location then the automatic generated code. And I am following the listed logic for generate this...

  Step 1: Generate the first three characters based on the location. Say Location is Detroit & Characters are DET.

  Step 2: Check if these characters match any other Locations first 3 characters.Step 3: Determine the Location which matches. Here it will match with Detroit Warehouse;If it matches then go to Step 4Step 4: Read the next character of the Location in Step 1 and match it with the Matched Location in Step 3. If matches repeat Step 4. Else Go to Step 5

  Step 5: Obtain the last character obtained in step 4 that did not match and  Append this unmatched character to the Prefix obtained in Step 1 i.e. W. Which makes the prefix as DETW.

  So for this what will be the best solution to get the correct result.

   Please help me, it is very urgent...

   Thanks in advance.

  Report abuse
• • Reply

  13 years ago

  by joe90

  J Alan

  , United Kingdom

  Joined 15 years ago

  Given that your logic follows:

  Step 1: Generate the first three characters based on the location.
  
  Step 2: Check if these characters match any other Locations first 3 characters.
  
  Step 3: Determine the Location which matches.
  
  Step 4: Read the next character of the Location in Step 1 and match it with the Matched Location in Step 3. If matches repeat Step 4. Else Go to Step 5
  
  Step 5: Obtain the last character obtained in step 4 that did not match and  Append this unmatched character to the Prefix obtained in Step 1 i.e. W. Which makes the prefix as DETW.
  
  Test: Location="Detroit"
  1 => Det
  2 => No <finished: presumably have now added Detroit to database table "Locations">
  
  Test: Location="Detroit Warehouse"
  1 => Det
  2 => Yes
  3 => Detroit
  4 => Detr
  4 => Detro
  4 => Detroi
  4 => Detroit
  4 => Detroit W (assuming we are not looking for spaces!)
  5 => DetW <finished: add to database>

  then the initial sp would be:

  create procedure prcAddLocation
  (
      @pname nvarchar(50)
  )
  as
  begin
      declare
          @code nvarchar(20),
          @name nvarchar(50),
          @len int
  
      set @code = substring(@pname, 1 , 3)
  
      select @name = [Name]
      from dbo.Location
      where (code like rtrim(@code) + '%')
  
      if (@name is null)
      begin
          insert Location (code, [name])
          values (@code, @pname)
      end
      else
      begin
          set @len = 3
  
          while (@len <= len(@pname))
          begin
              set @code = substring(@pname, 1 , @len)
  
              if not exists
              (
                  select [Name]
                  from dbo.Location
                  where [Name] like rtrim(@code) + '%'
              )
              begin
                  insert Location (code, [name])
                  values (substring(@pname, 1 , 3) + substring(@pname, @len , 1), @pname)
  
                  return
              end
             
              set @len = @len + 1
          end
         
      end
  end
  

  It does have a bug though, which you may not be aware of: IF table already contains say "Detroit Warehouse (spares)" then trying to add "Detroit Warehouse" will not do anything. You may therefore need to adjust your logic... but the above will be a good starting point.

  Joe 

  Report abuse
• • Reply

  13 years ago

  by windshadow2005

  Rajesh Kumar

  , India

  Joined 16 years ago

  Thanks a lot Joe. Your logic is working fine. But after changing of scope document this sprocedure will no longer used.

   Thanks again

  Regards

  Rajesh

  Report abuse