length of zipcode

[cmsbuffet]

I need a stored procedure. The stored procedure will analyze the length of the zip code. If the length is 5 you select against the USA table, if it is 6 (with no space) or 7 (with space) select from the Canada table. How do I start? What command parses a varchar zipcode?

 

[SJPS06]

Roughly (you might want to double check some of the syntax)


Create PROC usp_GetZip
@PostalCode varchar(7)
AS
BEGIN

IF len(@PostalCode) = 5
BEGIN
Select Column from ZipCodeUS where ZipCode = @PostalCode
END
ELSE
BEGIN
Select Column from ZipCodeCanada where ZipCode = @PostalCode

END

END

Question? Why put them in seperate tables, why not create one super PostalCode table with postal codes (they are only called Zip Codes in the US) for both countries?

 

[cmsbuffet]

I have a table with, both, zipcodes and postalcodes together. I need to create procedure that will allow to enter a zipcode or postal code and a distance, and it will call zipcodes or postal codes within that distance.

 

[SJPS06]

Do you store Latitude\Longitude of each Postal code? Is that what you are using to determine distance?

 

[cmsbuffet]

Yes. I can show you the procedure. At the end I will have to create a web page with a button and a textbox, which will call all the zipcodes or postalcodes within the distance entered in the textbox.

 

[SJPS06]

OK post the proc it might help.

 

[cmsbuffet]

use zipcode
go
If Exists(Select * From Information_Schema.Tables Where Table_Name = 'ZipCodes' And Table_Type = 'Base Table')
    Drop Table ZipCodes

CREATE TABLE ZipCodes(
    Country varchar(2) NULL,
    ZipCode varchar(5) NULL,
    City varchar(200) NULL,
    state varchar(50) NULL,
    StateAbbreviation varchar(2) NULL,
    County varchar(50) NULL,
    Unused1 varchar(5) NULL,
    Unused2 varchar(1) NULL,
    Latitude Decimal(8,5) NULL,
    Longitude Decimal(8,5) NULL,
    Unused3 varchar(1) NULL
)
Alter Table ZipCodes Drop Column Unused1, Unused2, Unused3


Declare @bulk_cmd varchar(1000)
Set @bulk_cmd = 'BULK INSERT ZipCodes
FROM ''c:\YourFolder\correct_database.csv''
WITH (FIELDTERMINATOR='';'', ROWTERMINATOR = ''\n'', LASTROW = 45234)'
EXEC(@bulk_cmd)


select * from zipcodes

Create Function dbo.LongitudePlusDistance
    (@StartLongitude Float,
    @StartLatitude Float,
    @Distance Float)
Returns Float
AS
Begin
    Return (Select @StartLongitude + Sqrt(@Distance * @Distance / (4784.39411916406 * Cos(2 * @StartLatitude / 114.591559026165) * Cos(2 * @StartLatitude / 114.591559026165))))
End

Create Function dbo.LatitudePlusDistance(@StartLatitude Float, @Distance Float) Returns Float
As
Begin
    Return (Select @StartLatitude + Sqrt(@Distance * @Distance / 4766.8999155991))
End


Create Function dbo.CalculateDistance(@Longitude1 Float, @Latitude1 Float, @Longitude2 Float, @Latitude2 Float)
Returns Float
AS
Begin
    Return Sqrt(Power(@Longitude2 - @Longitude1, 2) * Power(Cos((@Latitude1 + @Latitude2) / 114.591559026164646), 2) * 12391523883.7963953387686623629457364 + Power(@Latitude2 - @Latitude1,2) * 12346214104.563361) / 1609.344
End 


CREATE PROCEDURE cst_Find_Duplicates_In_Table(
        @Table    VARCHAR(25),
        @Field    VARCHAR(100))

AS
--DECLARE @Table AS VARCHAR(25)
--DECLARE @Field AS VARCHAR(100)

--SET @Table = 'Table'
--SET @Field = 'Field' --Name of Field that will determine if you have a duplicate
DECLARE @Sql AS VARCHAR(8000)

SET @Sql = 'SELECT ' + @Field + ', COUNT(' + @Field + ') AS NumCount ' +
            ' FROM ' + @Table +
            ' GROUP BY ' + @Field +
            ' HAVING (COUNT(' + @Field + ') > 1) ' +
            ' ORDER BY ' + @Field + ';'

EXEC(@Sql);

I also need

- the stored procedures must have a drop, create and grant execute to the read only user
- the functions must have a drop, create and grant execute / select to the read only user
- the .net files must be provided. I prefer .net 1.14