Parameterized dynamic sql returns no results

Question

From a C# application I create the following parameterized dynamic sql statement as captured by SQL profiler:

Executing this statement returns no results:

exec sp_executesql N'SELECT IDWebFormAnswer FROM WebFormAnswers WHERE IDWebform = 55 AND IDWebFormQuestion = 478 AND (ANSWER = ''@answer0'')', N'@answer0 nvarchar(2)', @answer0=N'XL'

However, if I simply replace @answer0 with XL in the following, I get 4 rows returned.

exec sp_executesql N'SELECT IDWebFormAnswer FROM WebFormAnswers WHERE IDWebform = 55 AND IDWebFormQuestion = 478 AND (ANSWER = ''XL'')', N'@answer0 nvarchar(2)', @answer0=N'XL'

I do not understand why this happens? Am I building the query wrong?

Answer 1

When you use a parameter you should not enclose it in quotes. If you do that then the parameter name becomes a literal string. With your code the query search for an ANSWER that contains the value '@Answer0' and there is none.

 exec sp_executesql N'SELECT IDWebFormAnswer FROM WebFormAnswers 
      WHERE IDWebform = 55 AND IDWebFormQuestion = 478 AND 
      (ANSWER = @answer0)', N'@answer0 nvarchar(2)', @answer0=N'XL'