stored procedure query loop
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].UKR开发者_C百科EGIONS
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
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.
精彩评论