SQL Exec Insert Integer Error?

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

WorknHardrAsked:
Who is Participating?
 
Paul JacksonConnect With a Mentor Software EngineerCommented:
try :

Declare @SQL varchar(max) ;
Set @SQL = 'Insert Into MyTableTest( [ListID] )' + ' Values(' + CAST(@ListID AS VARCHAR) + ')';
     Exec(@SQL);
0
 
Anthony PerkinsCommented:
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
 
WorknHardrAuthor Commented:
It worked, but why?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Paul JacksonConnect With a Mentor Software EngineerCommented:
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
 
WorknHardrAuthor Commented:
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
 
WorknHardrAuthor Commented:
>>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
 
Paul JacksonConnect With a Mentor Software EngineerCommented:
No not embedded that's something altogether different, its just called dynamic sql execution.
0
 
WorknHardrAuthor Commented:
Thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.