Solved

Dynamic SQL INSERT Error

Posted on 2008-06-13
4
178 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
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL HELP 2 94
Not selecting duplicate data 6 60
Problem with SqlConnection 4 178
How to simplify my SQL statement? 14 55
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard 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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

856 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