[sql] Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)

What are the real world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server using

EXEC (@SQL)

versus

EXEC SP_EXECUTESQL @SQL

?

I would always use sp_executesql these days, all it really is is a wrapper for EXEC which handles parameters & variables.

However do not forget about OPTION RECOMPILE when tuning queries on very large databases, especially where you have data spanned over more than one database and are using a CONSTRAINT to limit index scans.

Unless you use OPTION RECOMPILE, SQL server will attempt to create a "one size fits all" execution plan for your query, and will run a full index scan each time it is run.

This is much less efficient than a seek, and means it is potentially scanning entire indexes which are constrained to ranges which you are not even querying :@

Microsoft's Using sp_executesql article recommends using sp_executesql instead of execute statement.

Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server, sp_executesql is more efficient than EXECUTE.

So, the take away: Do not use execute statement. Use sp_executesql.

The big thing about SP_EXECUTESQL is that it allows you to create parameterized queries which is very good if you care about SQL injection.

1. Declare the variable
2. Set it by your command and add dynamic parts like use parameter values of sp(here @IsMonday and @IsTuesday are sp params)

3. execute the command

   declare  @sql varchar (100)
   set @sql ='select * from #td1'
   
   if (@IsMonday+@IsTuesday !='')
   begin
   set @sql= @sql+' where PickupDay in ('''+@IsMonday+''','''+@IsTuesday+''' )'
   end
   exec( @sql)