Solved

Dynamic SQL INSERT Error

Posted on 2008-06-13
4
180 Views
Last Modified: 2010-03-19
I am trying to create some dynamic SQL, when I hard code values in it works fine but when I try and put variables in I get the error message:

Msg 207, Level 16, State 1, Line 8
Invalid column name 'JewsonTest'.

Which is refering to:

SET @BranchCSVFileName = JewsonTest (this is a table name)

Can someone please tell me where I am going wrong??

My code is as follows:

DECLARE @BranchCSVFileName varchar(255)
DECLARE @lat numeric(18,6)
DECLARE @lon numeric(18,6)
DECLARE @MaxDistance int
DECLARE @dynamicdistancesql varchar(1000)
 
SET @BranchCSVFileName = JewsonTest
SET @lat = 51.400444
SET @lon = -1.318205
SET @MaxDistance = 10
SET @dynamicdistancesql = 'INSERT INTO '+@BranchCSVFileName+' (DistanceMiles, Postcode, Lat, Lon)
SELECT * FROM (SELECT CAST(dbo.Distance('+@lat+', '+@lon+', Lat, Lon) as decimal (10,1)) As DistanceMiles, [Postcode], [Lat], [Lon]
FROM [Postcodes]
WHERE (Lat <> 0) AND (Lon <> 0)) As T WHERE DistanceMiles<='+@MaxDistance+' ORDER BY DistanceMiles'
Exec (@dynamicdistancesql)

Open in new window

0
Comment
Question by:james1977
[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
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 200 total points
ID: 21777509
Hello james1977,

It still needs to be quoted as it is a string value:
Set @BranchCSVFileName = 'JewsonTest'

Regards,

TimCottee
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 21778560

DECLARE @BranchCSVFileName varchar(255)
DECLARE @lat varchar(40)  ---numeric(18,6)   change 1 
DECLARE @lon varchar(40)  ---numeric(18,6)   change 2
DECLARE @MaxDistance varchar(10) --- int     change 3
DECLARE @dynamicdistancesql varchar(1000)
 
SET @BranchCSVFileName = 'JewsonTest'
SET @lat = 51.400444
SET @lon = -1.318205
SET @MaxDistance = 10
SET @dynamicdistancesql = 'INSERT INTO '+@BranchCSVFileName  +' (DistanceMiles, Postcode, Lat, Lon)
SELECT * FROM (SELECT CAST(dbo.Distance('+@lat+', '+@lon+', Lat, Lon) as decimal (10,1)) As DistanceMiles, [Postcode], [Lat], [Lon]
FROM [Postcodes]
WHERE (Lat <> 0) AND (Lon <> 0)) As T WHERE DistanceMiles<='+@MaxDistance+' ORDER BY DistanceMiles'
EXEC( @dynamicdistancesql ) 

Open in new window

0
 

Author Comment

by:james1977
ID: 21778918
Ah I think i've got it, once I did as you suggested it came up with an error converting varchar to numeric so I changed all my variables to varchar and it ran fine.

Thanks very much for your helping hand.
DECLARE @BranchCSVFileName varchar(255)
DECLARE @lat varchar(20)
DECLARE @lon varchar(20)
DECLARE @MaxDistance varchar(10)
DECLARE @dynamicdistancesql varchar(1000)
 
SET @BranchCSVFileName = 'JewsonTest'
SET @lat = 51.400444
SET @lon = -1.318205
SET @MaxDistance = 10
SET @dynamicdistancesql = 'INSERT INTO '+@BranchCSVFileName+' (DistanceMiles, Postcode, Lat, Lon)
SELECT * FROM (SELECT CAST(dbo.Distance('+@lat+', '+@lon+', Lat, Lon) as decimal (10,1)) As DistanceMiles, [Postcode], [Lat], [Lon]
FROM [Postcodes]
WHERE (Lat <> 0) AND (Lon <> 0)) As T WHERE DistanceMiles<='+@MaxDistance+' ORDER BY DistanceMiles'
Exec (@dynamicdistancesql)

Open in new window

0
 

Author Comment

by:james1977
ID: 21778947
Sorry aneeshattingal i had already sorted the problem before you posted, just got delayed replying due to some other work.

Thanks for your input guys.

James.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 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