Yesterday in the Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly post I showed you that sp_executesql is better than exec because you get plan reuse and the procedure cache doesn't get bloated.
Today I will show you that sp_executesql is better than exec or ad hoc queries when you deal with conversions in execution plans

First create this table and populate it with some data

Take a look this query

Run the query

Here is the execution plan

|--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]),
WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=CONVERT_IMPLICIT(smallint,[@2],0)
AND [testpage].[dbo].[TestPerf].[SomeCol1]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)))

As you can see the value 1 needs to be converted to smallint and 'Test' Needs to be converted to nvarchar. You can actually see what the value is converted to by using the SQL_VARIANT_PROPERTY function.
When you run the following query

you get this as output

Base Type	PRECISION	Scale
int	        10	        0
varchar	        0	        0

so the value 1 becomes an int and 'test' is varchar

Exec with dynamic SQL is not any better than ad hoc of course since the same query gets generated as with the ad hoc query
Here is such a query

Here is the execution plan

 |--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]), 
 WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=CONVERT_IMPLICIT(smallint,[@2],0) 
 AND [testpage].[dbo].[TestPerf].[SomeCol1]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)))

Ideally you want a query with parameters; below is the quey

Here is the execution plan

  
 |--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]),
 WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=[@SomeValue] 
 AND [testpage].[dbo].[TestPerf].[SomeCol1]=[@SomeCol1]))

As you can see no conversions here

Now let's take a look at how we can take the dynamic query from before and use sp_executesql to get rid of conversions

Run this query

Here is the execution plan

As you can see there were no conversions.

So for this part of the query

   select @SQL = 'select * from dbo.TestPerf
   where SomeCol1 = @InnerSomeCol1 
   and SomeValue =  @InnerSomeValue' 

The SomeCol1 column and the @InnerSomeCol1 param/variable have to be of the same datatype,
the SomeValue column and the @InnerSomeValue param/variable also have to be of the same datatype in order to prevent conversions

Let's look at something else
The query from before with parameters

Here is the execution plan

  
 |--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]), 
 WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=[@SomeValue] 
 AND [testpage].[dbo].[TestPerf].[SomeCol1]=[@SomeCol1]))

what happens if you change the params to this

Here is the execution plan

  
 |--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]), 
 WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=[@SomeValue] 
 AND [testpage].[dbo].[TestPerf].[SomeCol1]=CONVERT_IMPLICIT(nvarchar(20),[@SomeCol1],0)))

We still get a conversion on the nvarchar SomeCol1 column

What happens if we change the outer parameter data types in the following dynamic query?

Here is the execution plan

Now we will change the parameters from smallint and nvarchar to int and varchar in the query below. This won't make any difference in the execution plan because the parameters used in the plan are still of the correct datatype since the inner parameters are used not the outer ones!!

Here is the execution plan

That wraps up this blog post

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum