If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Compare with multiple date values passed into stored procedure?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,719
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Tags
multiple dates, sql server, stored procedure

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On