code_junkie

Can you parameterize sort order in a sql server prepared statement

Nov 13th, 2011
164
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.41 KB | None | 0 0
  1. Dim command As SqlCommand = New SqlCommand("", connection)
  2. command.CommandText = "SELECT * FROM someTable ORDER BY orderValue @SortOrder"
  3. Dim sortParam As SqlParameter = New SqlParameter("@SortOrder", SqlDbType.varChar, 3)
  4. sortParam.Value = "ASC"
  5. command.Parameters.Add(sortParam)
  6. command.Prepare()
  7. reader = command.ExecuteReader()
  8.  
  9. declare @fname int,@lname int
  10.  
  11. select @fname=1,@lname=0
  12.  
  13. select * from [user]
  14. order by case when @Fname=1 then firstname when @lname=1 then lastname end
  15.  
  16. declare @firstSortField int, @secondSortField int
  17. set @firstSortField = 1
  18. set @secondSortField = 3
  19.  
  20. select firstName, lastName, phoneNumber
  21. from customers
  22. order by
  23. case @firstSortField when 1 then firstName when 2 then lastName when 3 then phoneNumber else null end,
  24. case @secondSortField when 1 then firstName when 2 then lastName when 3 then phoneNumber else null end
  25.  
  26. -- Testcase setup
  27. DECLARE @OrderASC bit
  28. SET @OrderASC = 0
  29.  
  30. -- Statement required
  31. SELECT
  32. Users.*,
  33. CASE @OrderASC WHEN 1 THEN Users.Alias ELSE null END AS _SortASC,
  34. CASE @OrderASC WHEN 0 THEN Users.Alias ELSE null END AS _SortDESC
  35. FROM Users
  36. ORDER BY _SortASC, _SortDESC DESC
  37.  
  38. -- Testcase setup
  39. DECLARE @OrderASC bit
  40. SET @OrderASC = 0
  41.  
  42. -- Statement required
  43. SELECT
  44. Users.*,
  45. CASE @OrderASC WHEN 1 THEN Users.Alias ELSE null END AS _SortASC,
  46. CASE @OrderASC WHEN 0 THEN Users.Alias ELSE null END AS _SortDESC
  47. FROM Users
  48. ORDER BY _SortASC, _SortDESC DESC
Add Comment
Please, Sign In to add comment