2

I would like auto append the where clause depending on the values supplied

Let us consider a stored procedure that contains a select query

CREATE PROCEDURE [dbo].[example]
    @From DATETIME,
    @T0 DATETIME
AS
  BEGIN TRY
      SELECT * 
      FROM footable 
      WHERE Fromdate = @From AND Todate = @TO
 END TRY

If the value of @From is null then the where clause must be 

SELECT * 
FROM footable 
WHERE Todate = @TO

Is there any way to implement this ?

Thanks a lot for the answer. but this does not solve my problem either because @From and @To both are optional. Following this approach my code would like 

SELECT * 
FROM footable 
WHERE (Fromdate = @From OR @From IS NULL)
  AND (Todate = @TO OR @To IS NULL)

Executing this results in the entire table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sri
  • 47
  • 1
  • 8
  • 2
    The solution from Andrew is a great way to deal with this. But be careful, you can run into performance issues with this especially as the number or parameters increases. Gail Shaw has a great article on this type of thing here. https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ – Sean Lange May 17 '18 at 13:32
  • 1
    Possible duplicate of [How can I use optional parameters in a T-SQL stored procedure?](https://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure) – EzLo May 17 '18 at 13:34
  • 2
    I'm not sure that you're asking the right question, it looks like you want to filter data that falls between your date parameters, and this won't do that as you're matching date**times** with `@from = fromdate`. apologies if i'm wrong – Tanner May 17 '18 at 13:37
  • @Tanner - I think you are right, but given its from `footable` I'm not sure the code is representative of a proper procedure, but just there for the question. The parameter `@T0` is then `@TO` later (Zero to O) – Andrew May 17 '18 at 13:39
  • @Tanner : you are absolutely right. this is not the exact code am trying to write. But just there for the question.@Andrew : Absolutely right sir – Sri May 17 '18 at 13:47

1 Answers1

8

Just use normal logic to handle it.

SELECT * 
FROM footable 
WHERE (Fromdate = @From or @From is null)
AND Todate = @TO
option (OPTIMIZE FOR (@From UNKNOWN))

Edit : Added the option clause in, so that the query plan is optimized for an unknown value and not subject to parameter sniffing to create the initial plan in the cache. Alternatively if the usage is known, you could use a known value that generates the plan you wish.

I would choose this option first before going down the route of an option (recompile) - which requires the plan being generated each time regardless.

Andrew
  • 26,629
  • 5
  • 63
  • 86
  • That will definitely work, however very possible that there will be performance problem. If you run this stored procedure with the NOT NULL parameter set, then it will generate the plan that would be used for NULLs also. – Dmitrij Kultasev May 17 '18 at 13:44
  • Yes, stable plans with this kind of query are problematic - its a valid call out, I'll add the option in to limit that – Andrew May 17 '18 at 13:46
  • 1
    Love the `OPTIMIZE FOR` suggestion. – Gordon Linoff May 17 '18 at 14:04