Conversion failed when converting the varchar value ' <> 0 then ' to data type int.

[JazzMaan]

I am trying to run below and get this error even though the columns is a integer.???

CAST(@CAMPCOUNT AS INTEGER) IS THE Issue in below query:

DECLARE
@CAMPCOUNT INTEGER,...


SET @SQLinsert = 'INSERT INTO ' + @DBName + 'WAVE_QC_HISTORY
(Exec_ID, Test_Number, Test_Name, Test_Result, Pass_Fail)
SELECT ' + CAST(@EXEC_ID AS VARCHAR(10)) + ', 1
,' + '''Find any xyz''' + '
, count(*)
, case when (count(*) - '+ CAST(@CAMPCOUNT AS INTEGER) + ' <> 0 then ' + '''Failed''' + ' else ' + '''Passed''' + ' end

FOLLOWING works:
SET @SQLinsert = 'INSERT INTO ' + @DBName + 'WAVE_QC_HISTORY
(Exec_ID, Test_Number, Test_Name, Test_Result, Pass_Fail)
SELECT ' + CAST(@EXEC_ID AS VARCHAR(10)) + ', 1
,' + '''Find any xyz''' + '
, count(*)
, case when (count(*) <> 0 then ' + '''Failed''' + ' else ' + '''Passed''' + ' end

 

[bborissov]

When you concatenate string you ALWAYS should use strings.
You cant add 0 (zero) to "somestring"

DECLARE @Test1 as varchar(10)
DECLARE @Test2 as int

SET @Test1 = 'something'
SET @Test2 = 0

SELECT @Test1 + @Test2 -- BOOOOM
-- but
SELECT @Test1 + CAST(@Test2 as varchar(10)) -- something0
[code]




Borislav Borissov
VFP9 SP2, SQL Server

 

[JazzMaan]

Solution is: Use If then for comparison

By design as i Understand you can not do a comparison using CASE with two variables.

I ended up doing something like:

Set @count1 = (select....)
Set @count 2 = (select count(*) from....)

If @count1 = @count 2
.....
Else
.....