Question

Which of the following is a calling syntax for sp_executesql?

(a) sp_execute <@stmt> [<@param1 data_type>,<@param2 data_type>, …]

(b) sp_sql <@stmt> [<@param1 data_type>,<@param2 data_type>, …]

(c) sp_executesql <@stmt> [<@param1 data_type>,<@param2 data_type>, …]

(d) sp_executesql [<@param1 data_type>,<@param2 data_type>, …]

I had been asked this question by my college director while I was bunking the class.

This is a very interesting question from Dynamic SQL topic in division Developing with SQL Server of SQL Server

Answer 1

The correct answer is (c) sp_executesql <@stmt> [<@param1 data_type>,<@param2 data_type>, …]

For explanation I would say: Using sp_executesql to run dynamic statements gives us a couple advantages over EXEC that are worth noting.