stored procedure - execution plan

[bperry]

I am writing a stored procedure that will return a record set via ADO.
I want to vary the ORDER BY clause based on a parameter that I will pass in.
ie
If @parm= 1 Select blahblahblah Order by Fld1
If @parm= 2 Select blahblahblah Order by Fld2
If @parm= 3 Select blahblahblah Order by Fld3

Other aspects of the Select are always identical, just the Order By changes.

I wonderif this will screw up the execution plan; should I make three SPs instead?
Any thoughts?

 

[tlbroadbent]

You may want to alter the SP somewhat.

Declare @sql nvarchar(4000)

Set @sql='Select blahblah....' +
case @param
When 1 Then ' Order By fld1'
When 2 Then ' Order By fld2'
When 3 Then ' Order By fld3'
End

Exec (@sql) Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[ClayG]

Looking at a query plan for the first approach, ie, separate selects chosen by ifs, each select appears in the query plan, so this would seem to be good - each option is optimised separately.

Building the statement dynamically (in a string) is more likely to recompile the select each time, which is a greater overhead. Though the dynamic statement is more flexible.