I've made my select faster but ... - Advice please

[EriRobert]

I've been looking at some poorly performing queries. These queries contain conditional conditions of the form:

Where
   C_PartitionCode Like
      Case
         When @intPartitionCode = 0 Then '%'
	 Else Cast(@intPartitionCode as varchar(3))
      End And ... etc

When I build it up into a string and execute it, it performs well:

Set @vchSQL = ..... +
     'Where ' +
        Case C_PartitionCode When 0 Then ''
                             Else
                             'C_PartitionCode = ' + 
           Cast(@intPartitionCode as varchar(3)) + ' And ' 
        End
...
exec (@vchSQL)

I understand why it is quicker, but don't want to use this method. Is there a way of improving my original code, without going down the dynamic route, 'or' for example

Thanks in advance

 

[bborissov]

Where (@intPartitionCode = 0 OR 
       C_PartitionCode = Cast(@intPartitionCode as varchar(3))) AND

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[EriRobert]

Thanks Boris

This is significantly faster than the case statement version, but is still 25% slower than the dynamic one. Would you expect this?

Robert