Stored Procedure Error

I am attempting to create a variable temp table to capture all the zip codes in a particular county and then insert them into a permanent table.  I am getting the error "Incorrect syntax near '.'."  Any suggestions?  Thanks!

CREATE PROCEDURE dbo.VendorCoverageInsert
	(
	@UserId VarChar(50),
	@VendorId Varchar(50),
	@ddCounty varchar(30),
	@ddStateKey int
	)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Zips TABLE  
	(
	PostalCode varchar(5)
	)

		INSERT INTO @Zips (PostalCode) 
		SELECT        u.USPostalCode
		FROM            USCounty AS u 
		INNER JOIN State ON u.USStateAbbr = State.StateAbbr
		WHERE        (State.StateKey = @ddStateKey) AND (u.USCounty = @ddCounty)

		

	INSERT INTO VendorCoverageArea (CreatedUser, VendorId, ModifiedUser,ZipCode)
		SELECT @UserId, @VendorId,@UserId, USPostalCode 
		FROM Zips
		WHERE NOT EXISTS (SELECT * FROM VendorCoverageArea 
		Inner JOIN Zips vca.ZipCode=Zips.ZipCode
                    WHERE VendorId = @VendorId and  VendorCoverageArea.ZipCode=Zips.PostalCode  )

Open in new window

rcowen00Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
SELECT @UserId, @VendorId,@UserId, USPostalCode
            FROM Zips
            WHERE NOT EXISTS (SELECT * FROM VendorCoverageArea
            Inner JOIN Zips vca.ZipCode=Zips.ZipCode
                    WHERE VendorId = @VendorId and  VendorCoverageArea.ZipCode=Zips.PostalCode  )


you don't have an alias for VCA

new code:

      SELECT @UserId, @VendorId,@UserId, USPostalCode
            FROM Zips
            WHERE NOT EXISTS (SELECT * FROM VendorCoverageArea vca
            Inner JOIN Zips vca.ZipCode=Zips.ZipCode
                    WHERE VendorId = @VendorId and  VendorCoverageArea.ZipCode=Zips.PostalCode  )
0
rcowen00Author Commented:
I updated the SP and still getting the same error

ALTER PROCEDURE dbo.VendorCountyCoverageInsert
	(
	@UserId VarChar(50),
	@VendorId Varchar(50),
	@ddCounty varchar(30),
	@ddStateKey int
	)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Zips TABLE  
	(
	PostalCode varchar(5)
	)

		INSERT INTO @Zips (PostalCode) 
		SELECT        u.USPostalCode
		FROM            USCounty AS u 
		INNER JOIN State ON u.USStateAbbr = State.StateAbbr
		WHERE        (State.StateKey = @ddStateKey) AND (u.USCounty = @ddCounty)

		

	INSERT INTO VendorCoverageArea (CreatedUser, VendorId, ModifiedUser,ZipCode)
		SELECT @UserId, @VendorId, @UserId, USPostalCode 
            FROM Zips
            WHERE NOT EXISTS
		    (SELECT * FROM VendorCoverageArea AS vca
            JOIN Zips vca.ZipCode=Zips.ZipCode
                    WHERE VendorId = @VendorId and  vca.ZipCode=Zips.PostalCode  )

Open in new window

0
Kyle AbrahamsSenior .Net DeveloperCommented:
Also:
 JOIN Zips vca.ZipCode=Zips.ZipCode

should be
 JOIN Zips vca.ZipCode=Zips.PostalCode


and
      INSERT INTO VendorCoverageArea (CreatedUser, VendorId, ModifiedUser,ZipCode)
            SELECT @UserId, @VendorId, @UserId, USPostalCode
should be

INSERT INTO VendorCoverageArea (CreatedUser, VendorId, ModifiedUser,ZipCode)
            SELECT @UserId, @VendorId, @UserId, PostalCode
0
rcowen00Author Commented:
Still a no go:(

ALTER PROCEDURE dbo.VendorCountyCoverageInsert
	(
	@UserId VarChar(50),
	@VendorId Varchar(50),
	@ddCounty varchar(30),
	@ddStateKey int
	)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Zips TABLE  
	(
	PostalCode varchar(5)
	)

		INSERT INTO @Zips (PostalCode) 
		SELECT        u.USPostalCode
		FROM            USCounty AS u 
		INNER JOIN State ON u.USStateAbbr = State.StateAbbr
		WHERE        (State.StateKey = @ddStateKey) AND (u.USCounty = @ddCounty)

		

	INSERT INTO VendorCoverageArea (CreatedUser, VendorId, ModifiedUser,ZipCode)
		SELECT @UserId, @VendorId, @UserId, USPostalCode 
            FROM Zips
            WHERE NOT EXISTS
		    (SELECT * FROM VendorCoverageArea AS vca
            JOIN Zips vca.ZipCode=Zips.PostalCode
                    WHERE VendorId = @VendorId and  vca.ZipCode=Zips.PostalCode  )

Open in new window

0
Kyle AbrahamsSenior .Net DeveloperCommented:
The only other thing I'm not sure about is this:

SELECT * FROM VendorCoverageArea AS vca


I've always done
select * from VendorCoverageArea vca   -- no as


Besides that, I would ensure that each of the columns referenced here exist in your tables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.