Solved

Stored Procedure Error

Posted on 2012-03-13
5
202 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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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 39

Accepted Solution

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now