Conversion failure varchar to int

Question

When running this code:

declare @SQL NVARCHAR(max);
declare @intFlag INT;
set @intFlag = 2;

SET @SQL = ' SELECT * into [##tmp1] 
FROM servicesstats_0511_0412 s WHERE department=''ACC''
and ((SELECT COUNT(*) FROM servicesstats_0511_0412 WHERE department <> ''ACC''
and s.studentid=studentid ) = ' + @intFlag + ')';

EXEC SP_EXECUTESQL @SQL 

I get the following error:

Conversion failed when converting the varchar value ' SELECT * into [##tmp1] FROM servicesstats_0511_0412 s WHERE department='ACC' and ((SELECT COUNT(*) FROM servicesstats_0511_0412 WHERE department <> 'ACC' and s.studentid=studentid ) = ' to data type int.

servicesstats_0511_0412 will be dynamic hence the need of dynamic SQL.

My belief is that this shouldn't occur since count(*) always returns an Int. Even when casting the subquery to an Int it doesn't work. Any thing i'm missing? thanks.

Answer 1

int has higher datatype precedence than [n]varchar so you need to cast the int explicitly to [n]varchar in the string concatenation to avoid the string being implicitly cast to an integer (which fails as the string is not numeric)

SET @SQL = N'
SELECT * into [##tmp1] 
FROM servicesstats_0511_0412 s 
WHERE department=''ACC''
AND ((SELECT COUNT(*) 
      FROM servicesstats_0511_0412 
      WHERE department <> ''ACC'' and s.studentid=studentid )
         = ' + CAST(@intFlag AS NVARCHAR(11)) + N')';

or don't concatenate it at all and pass it in as a parameter to sp_executesql instead.

SET @SQL = N'
SELECT * into [##tmp1] 
FROM servicesstats_0511_0412 s 
WHERE department=''ACC''
AND ((SELECT COUNT(*) 
      FROM servicesstats_0511_0412 
      WHERE department <> ''ACC'' and s.studentid=studentid ) = @intFlag)';

EXEC sp_executesql @SQL, N'@intFlag INT', @intFlag = @intFlag

Answer 2

While Martin has answered your question completely, I don't see why you would need dynamic SQL here at all.

declare @intFlag INT;
set @intFlag = 2;

SELECT *
into [##tmp1]
FROM servicesstats_0511_0412 s
WHERE department='ACC'
  and (SELECT COUNT(*)
      FROM servicesstats_0511_0412
      WHERE department <> 'ACC' and s.studentid=studentid ) = @intFlag;

Perhaps you missed the forest for the trees?

Answer 3

The problem is not count(*) here

declare @intFlag INT;  change -to    declare @intFlag varchar(20)='2'

Or

SET @SQL = ' SELECT * into [##tmp1] 
FROM servicesstats_0511_0412 s WHERE department=''ACC''
and ((SELECT COUNT(*) FROM servicesstats_0511_0412 WHERE department <> ''ACC'' and s.studentid=studentid ) = ' +CONVERT(varchar(100),@intFlag) + ')';