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

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.

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

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.