• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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

0
rcowen00
Asked:
rcowen00
  • 3
  • 2
1 Solution
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now