Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Compare with multiple date values passed into stored procedure?

    I have a scenario where I need to compare a single DateTime field in a stored procedure against multiple values passed into the proc.

    So I was thinking I could pass in the DateTime values into the stored procedure with a User Defined Table type ... and then in the stored procedure I would need to run through that table of values and compare against the CreatedWhenUTC value.

    Any suggestions for how this could actually work in syntax?

    I could have the following queries for example:

    WHERE CreatedWhenUTC <= dateValue1 OR CreatedWhenUTC <= dateValue2 OR CreatedWhenUTC <= dateValue 3

    The <= is determined by another operator param passed in. So the query could also be:

    WHERE CreatedWhenUTC > dateValue1 OR CreatedWhenUTC > dateValue2 OR CreateWhenUTC > dateValue3 OR CreateWhenUTC > dateValue4

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I would have questions as to why you are doing this in the manner you have described . . . but I will put aside the questions for now.

    I would suggest comparing your single date to the dates in the table and use the COUNT to indicate whether any record satisfied whatever condition you are looking to test.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,914
    This is really an application design problem more than a SQL problem.

    If you are working with an N-tier app design, move this code to an application server and iterate the comparisons there. If you are using a 2-tier design, refactor your application code if you can. If neither choice is viable, there is a SQL solution but it is messy.

    Create a table variable and populate it with your dateValue array. Pass the table variable to a stored procedure and use the table parameter to create a new instance of the same type that has only the matching member or members and return that to the client.

    Since the table parameter will create a truly ugly execution plan, there is no reason to avoid the added thrill of dynamic code to allow different comparison operators!

    Note that the first two options (fixing the design of the application code) are far preferable to trying to fix those problems using SQL. I would only use SQL to fix this problem as a last resort.

    -PatP

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Because there are a fairly finite combination of operations combinations, one could avoid dynamic SQL by simply testing for the operation and directing the code to the SQL appropriate to the operation. Again, this only assumes that one has a small number of operations combinations.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •