Dynamic SQL gives "Incorrect Syntax Near '+'"

Question

I am probably being extremely dumb here but why does this simple dynamic query:

EXEC sp_executesql N'SELECT Id FROM dbo.Widgets WHERE Id = ' + 1;

Give "Incorrect syntax near '+'"

?

Answer 1

You can't formulate the expression as part of the argument. And you shouldn't be concatenating that way anyway (think SQL injection) - you're using sp_executesql already, why not use a proper parameter?

DECLARE @Id INT, @sql NVARCHAR(MAX);
SET @Id = 1; -- presumably this will come from elsewhere

SET @sql = N'SELECT Id FROM dbo.Widgets WHERE Id = @Id;';
EXEC sp_executesql @sql, N'@Id INT', @Id;