What is the advantage of using @ParmDefinition in sp_executesql

Question

DECLARE @id int
DECLARE @name nvarchar(20)
SET @id = 5
SET @name = 'Paul'

What is the difference between these two options:

Set @SQLQueryInnen = 'SELECT * FROM someTable WHERE ID = ' + @id + ' AND NAME = ''' + @name + ''''
Execute sp_Executesql @SQLQueryInnen

and

Set @SQLQueryInnen = 'SELECT * FROM someTable WHERE ID = @id AND NAME = @name'
Set @ParmDefinition = '@id int, @name nvarchar(20)'
Execute sp_Executesql @SQLQueryInnen, @ParmDefinition, @id

So far I only see the overhad for declaring the data type of @id and @name twice, when using @ParmDefinition. On the other hand, the "string-building" seems a bit easier with @ParamDefinition.

Answer 1

You avoid having stringly-typed code - where you have to convert everything into a string so that you can shove it into the @SQLQueryInnen parameter, and then introduce issues because you have to work out how to safely and unambiguously perform the conversions to and from the strings back into the correct original data types.

For ints, the conversion issues aren't very apparent. But if you look at the number of issues people report (here, and on other forums) where they have issues converting between datetimes and strings, you'll realise that it does cause real issues. Best to keep the data as its natural type throughout.