Dynamic SQL Not Converting VARCHAR To INT (shouldn't anyway)
Solution 1
You need to convert your @Start
to a varchar
.
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'INSERT INTO TableRowCount (IntFieldID, DecimalField)
SELECT ' + CAST(@start as nvarchar(20)) +', COUNT(*)
FROM ' + @conn
SQL Server implicitly converts between datatypes on concatenation or addition based on some fairly complex criteria. Suffice to say if you try to combine an int
and a string it will always attempt to convert the string to an int
unless you tell it otherwise explicitly.
Below is a conversion chart for your reference from MSDN.
Solution 2
A better way than trying to concatenate an integer is to pass it in as a strongly-typed parameter:
DECLARE @start INT = 1;
DECLARE @sql NVARCHAR(MAX) = N'INSERT ...
SELECT @start, COUNT(*) FROM ' + @conn;
EXEC sp_executesql @sql, N'@start INT', @start;
Comments
-
Question3CPO almost 2 years
I'm receiving an error:
Conversion failed when converting the varchar value 'INSERT INTO TableRowCount (IntFieldID, DecimalField) SELECT 'to data type int"
Using the following code:
DECLARE @start INT -- @start is an INT SET @start = 1 -- INT DECLARE @sql NVARCHAR(MAX) SET @sql = 'INSERT INTO TableRowCount (IntFieldID, DecimalField) SELECT ' + @start +', COUNT(*) FROM dbo.somewhere' -- location is irrelevant EXECUTE(@sql) -- this is where it fails
If I remove
IntFieldID
and the@start
, it will work with an insert (though it defeats the purpose). I've tried including aSELECT CAST(' + @start + ' AS INT)
, which seems a little redundant since@start
is anINT
already (casting anINT
as anINT
), but that doesn't work either. I also tried beginning with anN'
DYNAMIC-SQL, which didn't work, I tried using three'''
around everything (didnt' work), and in a few places that I read online, responses suggested putting the variable in the string, which generated the error:Must declare scalar variable @start
(no surprise, as that didn't sound correct).