stored procedure query loop

Question

i have this stored procedure to look up uk postcodes..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_postcode_UK] 
    -- Add the parameters for the stored procedure here 
    @post_code varchar(10)

AS
DECLARE @intFlag INT
SET @intFlag = 4
WHILE (@intFlag >=1)
BEGIN

    SET NOCOUNT ON;  

    SELECT top 1 id,lat,lng from [postcodes].[dbo].UKREGIONS
    where postcode = left(@post_code,@intFlag)
    order by newid()

    IF @@rowcount > 0
    BREAK;
    SET @intFlag = @intFlag - 1
    END
GO

basically i havea database with the main regions and their geo positions.. so a postcode of w140df will belong to w14 in the database... sometimes it goes back to just one letter.. how do i do it so the stored procedure doesnt return blank records for the first couple of searches

Sklivvz · Accepted Answer · 2011-02-04 16:17:26Z

7

You can do it with (assuming you really want the longest match as it's more precise):

SELECT top 1 id,lat,lng from [postcodes].[dbo].UKREGIONS
where postcode IN (
  left(@post_code,1), left(@post_code,2),
  left(@post_code,3), left(@post_code,4)
)
ORDER BY LEN(postcode) DESC

without any need for looping.

You can also use like, but I think it would perform worse.

SELECT top 1 id,lat,lng from [postcodes].[dbo].UKREGIONS
where @post_code LIKE postcode+'%'
ORDER BY LEN(postcode) DESC

Note the inverted order of parameter and column in the LIKE clause.

answered Feb 4, 2011 at 16:17

Sklivvz

31.3k24 gold badges123 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andriy M Over a year ago

+1. What a brillian idea to break down LIKE into a set of substrings!

Collectives™ on Stack Overflow

stored procedure query loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related