Zipcode stored proc.

tthellebuyck

I need some help writing a stored proc. First, I am inserting a range of ZipCodes into a db, ZipBegin and ZipEnd(12345-12346). Although that seems relatively simple, however, I need to check those ranges and make sure that there is no other over lapping range. for more clarity: range A 12345-12346 is in the db range B 12344-12347 is being inserted. as you can see these ranges overlap, if that occurs I need to use a raiserror and rollback the transaction. Does anyone have any suggestions on how I could do that? Chance favors the prepared mind....

andyharman

Something like:

create procedure dbo.usp_InsertZipCodeRange
@StartZip int,
@EndZip int
as begin
declare @CurrentZip int

if (@StartZip > @EndZip or @StartZip < 0) begin
raiserror 'Invalid arguments specified'
return @@ERROR
end
if exists (select 1 from dbo.ZipCodes
where ZipCode between @StartZip and @EndZip) begin
raiserror 'Specified range overlaps existing Zip codes'
return @@ERROR
end

set @CurrentZip = @StartZip
while (@CurrentZip <= @EndZip) begin
insert into dbo.ZipCodes (ZipCode) values (@CurrentZip)
set @CurrentZip = @CurrentZip + 1
end

return 0
end

If Zip codes are strings then Right('00000' + Convert(varchar, @CurrentZip), 5) should allow you to convert an integer value into the appropriate string. Regards Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".