Passing Multiple Parameters to sp_executesql

The BOL rather unhelpfully doesn’t give an example of how to pass multiple parameters to sp_executesql, leaving one to wonder whether it’s

EXEC sp_executesql @sql, N'@start DATETIME', @start, N'@finish DATETIME', @finish

or possibly

EXEC sp_executesql @sql, N'@start DATETIME, @finish DATETIME', @start, @finish

(That N is important by the way — the documentation also doesn’t mention that the SQL statement and the parameter definitions must be NVARCHARs, not VARCHARs, and the error message that sp_executesql returns is only vaguely helpful.)

It turns out that #2 is the correct syntax, though you wouldn’t know it from the intelligible error returned by sp_executesql. Thanks again, Google!

Comments are closed.