column as stored procedure parameter?

[Ed Hill _5_]

I have a storedProcedure i need to modify so it can sort by one of two columns, i need to allow the program calling the stored procedure to choose which one it wants the data sorted by, currently there are serveral if statments deciding exactly which select statment i will run, i need all of them to have the order by on the end as a variable passed into the stored procedure, is there any way to do this? my last try: if @Filter = 'All' and @DateFrom is Null and @MatterRef = '' BEGIN select * from undertakings order by case when @sortBy = 'matterRef' then undertakings.MatterRef when @sortBy = 'Date' then undertakings.DateTaken end END if @filter = 'Active' and @DateFrom is Null and @MatterRef = '' BEGIN select * from undertakings where Discharged = 0 order by case when @sortBy = 'matterRef' then undertakings.MatterRef when @sortBy = 'Date' then undertakings.DateTaken end END if @Filter = 'Discharged' and @DateFrom is Null and @MatterRef = '' BEGIN select * from undertakings where Discharged = 1 order by case when @sortBy = 'matterRef' then undertakings.MatterRef when @sortBy = 'Date' then undertakings.DateTaken end END the procedure is a little more complex, with several other condtions but you should be able to get the idea. Any help or pointers would be appreciated

[John ph]

when you have more than one filter criteria It's always a good practice to do it in a single dynamic query(T-Sql String). sp_executesql command can be used to execute a string and also it support's substituting any paramerter values that is specified in T-Sql String. I have gone through your Query and written a stored procedure. Hope it will help you to modify your's...

Create Procedure sp_undertakingsSelect
	@Filter NVarchar(50),
	@DateFrom DateTime,
	@MatterRef NVarchar(200),
	@sortBy NVarchar(50)
AS
  
	Set NoCount ON
  
	Declare @SQLQuery AS NVarchar(4000)
	Declare @ParamDefinition AS NVarchar(2000) 
  
	Set @SQLQuery = 'Select * From undertakings where (1=1) ' 
  
	If @Filter = 'Active'
	     Set @SQLQuery = @SQLQuery + ' And Discharged = 0' 
  
	If @Filter = 'Discharged'
	     Set @SQLQuery = @SQLQuery + ' And Discharged = 1'
  
	If @DateFrom Is Not Null
	     Set @SQLQuery = @SQLQuery + ' And DateTaken >= @DateFrom' 
  
	If @MatterRef <> '' 
	     Set @SQLQuery = @SQLQuery + ' And MatterRef LIKE '''+ '%' + @MatterRef + '%' + '''' 
  
	If @sortBy  = 'matterRef'
	     Set @SQLQuery = @SQLQuery + ' Order By MatterRef '
  
	If @sortBy  = 'Date'
	     Set @SQLQuery = @SQLQuery + ' Order By DateTaken'
  
	Set @ParamDefinition = '@Filter NVarchar(50),
  				@DateFrom DateTime,
  				@MatterRef NVarchar(200),
  				@sortBy NVarchar(50)'
  
	Exec sp_Executesql @SQLQuery, @ParamDefinition, @Filter, @DateFrom, @MatterRef, @sortBy
  
	If @@ERROR <> 0 GoTo ErrorHandler
	Set NoCount OFF
	Return(0)
  
ErrorHandler:
	Return(@@ERROR)
GO

Regards
J O H N :rose:
"Even eagles need a push." David McNally

---

[Ed Hill _5_]

Worked a treat, many thanks for taking the time to respond to my question.

[John ph]

Ed Hill _5_ wrote:

many thanks

You're most welcome

Regards
J O H N :rose:
"Even eagles need a push." David McNally

---