locked
Need help store Procedure RRS feed

  • Question

  • Hi Friends,

    please help me to create a new stored procedure function in location sql database to validate the city and zip combination, I mean that will do a lookup validation with city and zip code

    Thanks

    Maddy

    Tuesday, May 29, 2012 8:54 PM

Answers

  • Can you elaborate on your question? What kind of lookup do you want? Do you have a table of zipcodes?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by maddy28 Thursday, June 7, 2012 3:24 PM
    Tuesday, May 29, 2012 9:08 PM
  • I assume that you have a table that contains all the valid zipcodes and citys?

    You can then use something like the below code to validate if the combination exists -

    DECLARE @ZipCode NVARCHAR(20), @City NVARCHAR(50)
    
    SELECT @City = 'GG', @ZipCode = '22'
    
    IF OBJECT_ID('tempdb..#ZipCodeTable') IS NOT NULL
    DROP TABLE #ZipCodeTable
    
    CREATE TABLE #ZipcodeTable (ZipCode NVARCHAR(20), City NVARCHAR(50))
    
    INSERT INTO #ZipcodeTable VALUES ('11','TT'),('22','GG')
    
    IF EXISTS (SELECT * FROM #ZipcodeTable WHERE ZipCode = @ZipCode AND City = @City)
    BEGIN
    PRINT 'OK'
    END
    ELSE
    BEGIN
    PRINT 'Not OK'
    END
    
    IF OBJECT_ID('tempdb..#ZipCodeTable') IS NOT NULL
    DROP TABLE #ZipCodeTable 

    If you have all your "source" zipcodes and citys in a table (e.g. in an existing address table) you can do a join on the source and "reference" tables and check if the combinations exists - and then do what ever you need to do based on the result.


    Steen Schlüter Persson (DK)

    • Marked as answer by maddy28 Thursday, June 7, 2012 3:25 PM
    Wednesday, May 30, 2012 7:59 AM
  • Hi Maddy,

    Please see this article:
    http://www.htmlist.com/development/zip-codes-in-web-apps-a-tutorial-on-validating-cities-calculating-distance/

    It provides a statement:

    SELECT
    zip_codes.poname AS city,
    states.name AS state,
    states.abbreviation AS abrv
    FROM
    zip_codes
    LEFT JOIN
    states ON states.fips_code = zip_codes.state_fips_code
    WHERE
    zip_code =  {your_zip_code}
    


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Naomi N Thursday, May 31, 2012 1:06 PM
    • Marked as answer by Iric Wen Wednesday, June 6, 2012 2:23 AM
    Thursday, May 31, 2012 6:36 AM
  • Ok, the idea of the procedure will be similar to what Steen wrote.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by maddy28 Thursday, June 7, 2012 3:25 PM
    Thursday, May 31, 2012 1:06 PM

All replies

  • Can you elaborate on your question? What kind of lookup do you want? Do you have a table of zipcodes?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by maddy28 Thursday, June 7, 2012 3:24 PM
    Tuesday, May 29, 2012 9:08 PM
  • I assume that you have a table that contains all the valid zipcodes and citys?

    You can then use something like the below code to validate if the combination exists -

    DECLARE @ZipCode NVARCHAR(20), @City NVARCHAR(50)
    
    SELECT @City = 'GG', @ZipCode = '22'
    
    IF OBJECT_ID('tempdb..#ZipCodeTable') IS NOT NULL
    DROP TABLE #ZipCodeTable
    
    CREATE TABLE #ZipcodeTable (ZipCode NVARCHAR(20), City NVARCHAR(50))
    
    INSERT INTO #ZipcodeTable VALUES ('11','TT'),('22','GG')
    
    IF EXISTS (SELECT * FROM #ZipcodeTable WHERE ZipCode = @ZipCode AND City = @City)
    BEGIN
    PRINT 'OK'
    END
    ELSE
    BEGIN
    PRINT 'Not OK'
    END
    
    IF OBJECT_ID('tempdb..#ZipCodeTable') IS NOT NULL
    DROP TABLE #ZipCodeTable 

    If you have all your "source" zipcodes and citys in a table (e.g. in an existing address table) you can do a join on the source and "reference" tables and check if the combinations exists - and then do what ever you need to do based on the result.


    Steen Schlüter Persson (DK)

    • Marked as answer by maddy28 Thursday, June 7, 2012 3:25 PM
    Wednesday, May 30, 2012 7:59 AM
  • Hi Maddy,

    Please see this article:
    http://www.htmlist.com/development/zip-codes-in-web-apps-a-tutorial-on-validating-cities-calculating-distance/

    It provides a statement:

    SELECT
    zip_codes.poname AS city,
    states.name AS state,
    states.abbreviation AS abrv
    FROM
    zip_codes
    LEFT JOIN
    states ON states.fips_code = zip_codes.state_fips_code
    WHERE
    zip_code =  {your_zip_code}
    


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Naomi N Thursday, May 31, 2012 1:06 PM
    • Marked as answer by Iric Wen Wednesday, June 6, 2012 2:23 AM
    Thursday, May 31, 2012 6:36 AM
  • Hi Naomi,

    Still i dont have more details on this requireement. i will come up with requirement soon i have it. Thanks for your help

    Thursday, May 31, 2012 10:12 AM
  • Thanks Steen
    Thursday, May 31, 2012 10:13 AM
  • Thanks Iric
    Thursday, May 31, 2012 10:13 AM
  • Ok, the idea of the procedure will be similar to what Steen wrote.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by maddy28 Thursday, June 7, 2012 3:25 PM
    Thursday, May 31, 2012 1:06 PM