Solved

Stored Procedure Error

Posted on 2012-03-13
5
205 Views
Last Modified: 2012-03-13
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
Comment
Question by:rcowen00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37716218
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
 

Author Comment

by:rcowen00
ID: 37716258
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37716312
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
 

Author Comment

by:rcowen00
ID: 37716326
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
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 37716425
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

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question