Solved

Stored Procedure Error

Posted on 2012-03-13
5
203 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
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

777 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