Stored Proc help
-
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?
-
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?
-
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?
You want to check before or after inserting? below logic will help you. Before Inserting ------------------ check whether there is any ZIPCode exist within the range sql --- Select @begin = count(ZIPBegin) from tblZIPCodes where ZIPBegin between 12345 and 12346 Select @end = count(ZIPEnd) from tblZIPCodes where ZIPEnd between 12345 and 12346 If @begin > 0 or @end > 0 -- then there is an entry within that range already exist else --perform the insertion After Inserting ---------------- BEGIN TRANSACTION --Insert the records -- then use the same sql stmt written above to get the value of @begin and --@end If @begin > 1 or @end > 1 -- multiple entries has been made ROLLBACK TRANSACTION RAISERROR RETURN else --perform the insertion COMMIT TRANSACTION
Regards
John