Solved

SQL Exec Insert Integer Error?

Posted on 2014-01-23
8
330 Views
Last Modified: 2014-01-24
I receive the following error trying to insert a known Int variable like so:

   Error: " Conversion failed when converting the varchar value 'Insert Into MyTableTest([ListID] Values(' to data type int.  "

Q1. What is the proper name for using Exec and variable as below, 'Embedded SQL?

Q2. Why is this Insert failing when I know 'MyTableTest' and 'ListID' is type Int?

Note: I think all the single and double-quotes are correct...

//calling code
exec sp_MyTableTest 1469

//SP
ALTER PROCEDURE [dbo].[SP_MyTableTest]
(
	@ListID		int
)

AS

BEGIN
     Declare @SQL varchar(max) = 'Insert Into MyTableTest( [ListID] )' + ' Values(' + @ListID + ')';
     Exec(@SQL);
END

Open in new window

0
Comment
Question by:WorknHardr
[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
  • 4
  • 3
8 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 400 total points
ID: 39805338
try :

Declare @SQL varchar(max) ;
Set @SQL = 'Insert Into MyTableTest( [ListID] )' + ' Values(' + CAST(@ListID AS VARCHAR) + ')';
     Exec(@SQL);
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39805349
Or simply lose the Dynamic SQL altogether (and the corresponding security risks involved) and do it like this:
//calling code
exec sp_MyTableTest 1469

//SP
ALTER PROCEDURE [dbo].[SP_MyTableTest]
(
	@ListID		int
)

AS

BEGIN
     Insert Into MyTableTest([ListID]) Values(@ListID)

END

Open in new window

0
 

Author Comment

by:WorknHardr
ID: 39805351
It worked, but why?
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 400 total points
ID: 39805359
When concatenating strings in tsql they have to be of the same type so you need to explicitly convert any integers to a varchar otherwise it tries to add them together.
0
 

Author Comment

by:WorknHardr
ID: 39805365
Wow, even though it's Inserted into an INT type column, I would have never guessed...

Q1. What is the proper name for using Exec and variable, 'Embedded SQL?
0
 

Author Comment

by:WorknHardr
ID: 39805369
>>Anthony Perkins

Thanks for your input and opinion. I have excluded other columns which make my crazy coding necessary. I have a very unusual project requirement, thanks again...
0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 400 total points
ID: 39805375
No not embedded that's something altogether different, its just called dynamic sql execution.
0
 

Author Closing Comment

by:WorknHardr
ID: 39806422
Thx
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

628 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