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.