code_junkie

SQL if statement in where clause for searching database

Nov 12th, 2011
68
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.13 KB | None | 0 0
  1. ALTER PROCEDURE spVillaGet
  2. -- Add the parameters for the stored procedure here
  3. @accomodationFK int = null,
  4. @regionFK int = null,
  5. @arrivalDate datetime,
  6. @numberOfNights int,
  7. @sleeps int = null,
  8. @priceFloor money = null,
  9. @priceCeil money = null
  10. AS
  11. BEGIN
  12. -- SET NOCOUNT ON added to prevent extra result sets from
  13. -- interfering with SELECT statements.
  14. SET NOCOUNT ON;
  15.  
  16. -- Insert statements for procedure here
  17. select tblVillas.*, tblWeeklyPrices.price from tblVillas
  18. INNER JOIN tblWeeklyPrices on tblVillas.villaId = tblWeeklyPrices.villaFK
  19. where
  20. If @accomodationFK <> null then
  21. accomodationTypeFK = @accomodationFK
  22. @regionFK <> null Then
  23. And regionFK = @regionFK
  24. IF @sleeps <> null Then
  25. And sleeps = @sleeps
  26. IF @priceFloor <> null Then
  27. And price >= @priceFloor And price <= @priceCeil
  28.  
  29.  
  30. END
  31.  
  32. select tblVillas.*, tblWeeklyPrices.price
  33. from tblVillas
  34. INNER JOIN tblWeeklyPrices on tblVillas.villaId = tblWeeklyPrices.villaFK
  35. where (@accomodationFK IS null OR accomodationTypeFK = @accomodationFK)
  36. AND (@regionFK IS null or regionFK = @regionFK)
  37. AND (@sleeps IS null OR sleeps = @sleeps)
  38. AND (@priceFloor IS null OR (price BETWEEN @priceFloor And @priceCeil))
  39.  
  40. SELECT *
  41. FROM vehicles
  42. WHERE ([vin] LIKE COALESCE(@vin, [vin]) + '%' ESCAPE '')
  43. AND ([year] LIKE COALESCE(@year, [year]) + '%' ESCAPE '')
  44. AND ([make] LIKE COALESCE(@make, [make]) + '%' ESCAPE '')
  45. AND ([model] LIKE COALESCE(@model, [model]) + '%' ESCAPE '')
  46.  
  47. SELECT *
  48. FROM vehicles
  49. WHERE (([vin] LIKE COALESCE(@vin, [vin]) + '%' ESCAPE '')
  50. OR (@vin IS NULL AND [vin] IS NULL))
  51. AND (([year] LIKE COALESCE(@year, [year]) + '%' ESCAPE '')
  52. OR (@year IS NULL AND [year] IS NULL))
  53. AND (([make] LIKE COALESCE(@make, [make]) + '%' ESCAPE '')
  54. OR (@make IS NULL AND [make] IS NULL))
  55. AND (([model] LIKE COALESCE(@model, [model]) + '%' ESCAPE '')
  56. OR (@model IS NULL AND [model] IS NULL))
  57.  
  58. Where accomodationTypeFK = IsNull(@accomodationFK, accomodationTypeFK)
  59. And regionFK = Coalesce(@regionFK,regionFK)
  60. And sleeps = IsNull(@sleeps,sleeps )
  61. And price Between IsNull(@priceFloor, Price) And IsNull(priceCeil, Price)
Add Comment
Please, Sign In to add comment