Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Why do you need dynamic SQL (see Richard's answer)? –  dezso Oct 30 '12 at 7:25
    
Because servicesstats_0511_0412 will be dynamic –  Marin Oct 30 '12 at 12:56
    
Please include such relevant information in your question next time. –  dezso Oct 30 '12 at 12:58
    
No problem. Will do :-) –  Marin Oct 30 '12 at 13:02

3 Answers 3

up vote 8 down vote accepted

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
share|improve this answer
    
Excellent thank you Martin. I chose the second way since it looks cleaner. Second parameter is type, third parameter is value being passed, but why do you have to pass it like so var = value ? –  Marin Oct 30 '12 at 13:12
1  
It's less ambiguous when you have a longer list, otherwise as with SP parameters you can omit the name and list them in the correct sequence, i.e. EXEC sp_executesql @SQL, N'@intFlag INT', @intFlag –  孔夫子 Oct 30 '12 at 13:15
    
Thanks makes sense. –  Marin Oct 30 '12 at 13:26

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?

share|improve this answer
    
servicesstats_0511_0412 will be dynamic. –  Marin Oct 30 '12 at 12:56

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) + ')';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.