Dynamic SQL with Explanation and Example

Dynamic SQL with Explanation and Example 

Dynamic SQL statements can be built at run time and placed in a string host variable.
They are then sent to the DBMS for processing. Because the DBMS must generate an access plan
 at run time for dynamic SQL statements, dynamic SQL is generally slower than static SQL.
 When a program containing dynamic SQL statements is compiled, the dynamic SQL statements are not
 stripped from the program, as in static SQL. Instead, they are replaced by a function call
 that passes the statement to the DBMS;
static SQL statements in the same program are treated normally
.
To Know more about Dynamic sql go to
 msdn link-http://msdn.microsoft.com/en-us/library/ms709342(v=vs.85).aspx 

Let us suppose a scenario where have to show data from a table and the table Data should be displayed based selection of table by user so here dynamic sql will come into picutre.

below is a simple stored procedure that will take table name as input

Create proc p
(@tblname nvarchar(100)
)
as
begin
declare @tbttest nvarchar(100)
set @tbttest ='select * from ' +@tblname+'';
--exec(@tbttest);
exec SP_executesql @tbttest 
end

exec  P 'test'

This question is often asked by interviewer to display data of Table at run time user will just pass the table name and you have to show the Data of that table.

Just pass your table name to procedure P it will display all Data of Test table you can pass any table name
You can execute dynamic sql  with Exec(@tbttest) or by executing System Procedure
exec SP_executesql @tbttest .

 sp_executesql is more efficient, faster in execution and also supports parameter substitution. If we are using EXECUTE command to execute the SQL String, then all the parameters should be converted to character and become as a part of the Query execution.