3

I am currently working on an asp.net application that has sql server 2008 as its backend. I want to give the user the ability to specify what they want to filter by on the SQL statement. On the interface I am giving them the option to select the following as a dropdown: equals to greater than Less than etc

I want to pass this as a parameter on the sql query to be executed. How best can I achieve this?

for eg; 

Select amount, deduction, month from loan where amount @operant 10000;

the @operand is the return values of the above dropdown which is = < > <= >=

Darshana
  • 2,462
  • 6
  • 28
  • 54
asembereng
  • 675
  • 2
  • 8
  • 18

3 Answers3

8

Assuming all positive integers < 2 billion, this solution avoids multiple queries and dynamic SQL. OPTION (RECOMPILE) helps thwart parameter sniffing, but this may not be necessary depending on the size of the table, your parameterization settings and your "optimize for ad hoc workload" setting.

WHERE [Amount] BETWEEN 
CASE WHEN @operand LIKE '<%' THEN 0
     WHEN @operand = '>' THEN @operant + 1
     ELSE @operant END
AND
CASE WHEN @operand LIKE '>%' THEN 2147483647
     WHEN @operand = '<' THEN @operant - 1
     ELSE @operant END
OPTION (RECOMPILE);
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • @buckley Why is this funny? If the portions of the query we can't see are extremely complex, do you think it's better to repeat the whole query 5 times? Dynamic SQL also becomes less desirable in that case because it's much less maintainable (mostly for the readability but also particularly if there are other parameters to the query we also can't see). It's just an option. You don't have to like it, but you don't have to laugh at it either. – Aaron Bertrand May 22 '12 at 14:28
  • Oops, I thought you were being funny, no harm intended on my part. I'll switch to professional mode then and would not buy you lunch since I have to maintain that. (Note that I am now I'm trying to be funny). This query does not communicate the intention of the programmer who originally wrote it. Code should convey intention and not try to be clever. And you are being clever here. Dynamic sql only differs from normal one in that you have to replace single quotes by 2 of them and do some concatenation/introduce placeholders. If you agree that this is the only diff than we differ in taste. – buckley May 22 '12 at 14:38
  • Clever is subjective and if it avoids some of the other limitations, and they're worse, I don't think they should be discarded. While I am all for making self-documented code, there is also no law against adding comments if something is intentionally unintuitive. Again I did not say "**YO, THIS RIGHT HERE IS BETTER IN ALL CASES!!!**" I am just offering it as an alternative option. Lots of "clever" solutions are still the best solution, even if an absolute novice doesn't get it on first glance. The primary goal of your code is usually perf / maintainability, not necessarily in that order. – Aaron Bertrand May 22 '12 at 14:42
  • And no, single quotes and placeholders/concatenation are not the only difference. With dynamic SQL you lose color coding in the editor, as well as both visual and runtime IntelliSense. Have you ever tried to debug a 500-line dynamic SQL statement, a.k.a. "the red sea"? – Aaron Bertrand May 22 '12 at 14:43
  • Good point about intellesense/color coding, that's annoying not to have – buckley May 22 '12 at 14:44
  • I think we agree in principle but not in gradation. I start of with highly maintainable code since 5% of a project is spent on writing it and 95% on maintaining it (don't now the exact typical numbers, the book "code complete" refers to some studies) and this is perfectly in line with what I see. Only when perf becomes an issue as surfaced by stress tests I'll introduce cleverness with the necessary comments. – buckley May 22 '12 at 14:50
  • @buckley why do you think that's not what I'm doing? Once again, I did not say my answer was *better* - just an option. – Aaron Bertrand May 22 '12 at 14:55
  • And just to be clear - I do like dynamic SQL, and use it a lot. You will find it in my answers to a *lot* of questions here on Stack Overflow. But as with many things, using dynamic SQL involves trade-offs. The solution I posted here offers an alternative, nothing more, nothing less. We have to solve problems like paging, splitting, concatenation, avoiding cursors - and we have clever tricks for those, too, that aren't self-explanatory. Should those be discarded because they require documentation? – Aaron Bertrand May 22 '12 at 15:06
  • I think we are now in the territory of taste which leads to questions as "What do you mean, you don't like strawberries?? What is there not to like?". I agree that you are answering the question and are still to the point. Let me use an argument by Reductio ad absurdum then and say that one could answer with code written in assembly or binary for that matter. It will probably be the fastest(one would have a difficult point explaining that to others however) and be a maintenance nightmare. Still, if your requirements are to really get to the metal that you should do it in assembly. – buckley May 22 '12 at 15:32
  • So yes, that is an option, but I make the assumption that maintainability should never be compromised unless perf optimization is part of the problem. In this case the OP was asking for the best way to formulate his dynamic condition and the question was not to squeeze ever last of perf out of the hardware. If he gets inspired by your answer and comes up with something we didn't think about (brainstorming) your answer was helpful. We agree that dynamic SQL involves trade-offs. As to the clever tricks for those that aren't self-explanatory like pagination, avoiding loops,... – buckley May 22 '12 at 15:32
  • I would make the point that they raise above cleverness and become *patterns*. Patterns are general solution that apply to many problems that come in many forms. Sure they are clever but they have conquered a place in the collective consciousness of a community as they have proven useful again and again in many different situation. IMHO I don't see this in your answer. I agree that it can make you think about a problem from a different perspective and thus is an intellectual exercise. Maybe that is what you mean with option? BTW SO is rightly point out that this is an extended discussion :) – buckley May 22 '12 at 15:33
  • If you don't see this in my answer then I don't think you have been exposed to enough situations where this is a potential answer. :-) This trick has often been used to avoid dynamic SQL and to avoid re-writing the query umpteen times. I didn't invent the methodology in the process of answering the question. – Aaron Bertrand May 22 '12 at 15:37
  • It's the first time ive seen it yes. Do you know of a blog post/book that discusses this pattern? Ive been doing sql development for 14 years now and such a construct is new to me. – buckley May 22 '12 at 16:16
  • I don't know what the pattern is called, so am no more equipped to search than you. I don't know how else I can convince you that I didn't just come up with some hairball scheme this morning... – Aaron Bertrand May 22 '12 at 16:28
  • I wasnt doubting your sincerity. Im glad we can have a good discussion and learned quit a bit. If I run into such code ill recognize it faster from now on – buckley May 22 '12 at 16:34
  • @buckley I also found that I have offered this type of solution before: http://stackoverflow.com/questions/6965242/sorting-nvarchar-column-containing-alphanumeric-values – Aaron Bertrand May 24 '12 at 20:49
  • Thanks for the follow up. What I am also wondering if any indexes are used. That where clause does not seem like a SARG right? – buckley May 25 '12 at 09:10
  • Should use an index just fine. – Aaron Bertrand May 25 '12 at 11:37
  • So that whole CASE WHEN ELSE does indeed translate to a sarg. I though that would be to messy for the optimizer to see through. That's pretty slick of the optimizer – buckley May 25 '12 at 21:34
  • 1
    Wow that is very creative. It rescues SARGability which I thought was impossible. – usr Jul 23 '13 at 22:15
0

I would write few "IF" statements. Code is not very short, but should be fast.

IF(@operand = '=')
Select..
ELSE IF(@operand = '>=')
Select..
...

Also, i would say, that Top (@someRowCount) could be great idea.

Jānis
  • 2,216
  • 1
  • 17
  • 27
  • That's what I ment with enumerating all the possible combinations. If the scenario of the OP stays as simple as this it's doable (5 variations) but it doesn't scale. From my dev background I would also say you violate the DRY principle a bit but since its so local the maintainer probably won't forget to adjust a change to 5 instead of 1 place. Still, not optimal IMO – buckley May 22 '12 at 13:50
  • Why would `TOP` be a great idea? The OP hasn't indicated that they only want `@someRowCount` rows. If there are 10,000 rows that match the criteria, why only show a subset? – Aaron Bertrand May 22 '12 at 14:02
  • @AaronBertrand good idea in case table have millions of rows.. Usually user doesn't care about more rows than can see in one page.. And as there was word "Usually", then i had included "could be" not "must have" – Jānis May 22 '12 at 14:07
  • That's a completely different implementation detail and actually changes the meaning and the output of the query. – Aaron Bertrand May 22 '12 at 14:09
-1

You need dynamic sql for this scenario

For your example this can be

DECLARE @sql AS nvarchar(max) -- Use max if you can, if you set 
  -- this to a specific size then your assignment later can be 
  -- truncated when maintained and still be valid.

SET @sql = 'Select amount, deduction, month from dbo.loan where amount ' 
  + @operand + ' 10000'

EXEC sp_executesql @sql

Update 1

There are 2 ways to execute dynamic sql : Exec() and sp_executesql

Read the comments why sp_executesql is preferred (still, beware of sql injections!)

I also prefix the table with the dbo so that the execution plan can be cached between different users

More info in the awesome paper at http://www.sommarskog.se/dynamic_sql.html#queryplans

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
buckley
  • 13,690
  • 3
  • 53
  • 61
  • One thing to watch out for when donig dynamic sql is "sql injection". There is lots of information on the net about it. To prevent an injection you better match @operand to the values you are expecting and if it's unexpected don't execute it – buckley May 22 '12 at 13:38
  • its not very efficient.. and security risks. – Jānis May 22 '12 at 13:38
  • 1
    @Janis It has a security risk that every dba/dev should be aware of. The other alternative, enumerating all possible combinations of query, is feasible here cause of the limited variation but is still cumbersome and not better than dynamic sql. Why do you think it's not efficient? Do you think that the execution plan is not cached? – buckley May 22 '12 at 13:42
  • and other thing to watch out is user rights. If you using "Exec (..)" then ownership chains is not working and user needs to have access to base tables or procedure should be signed or some other option (there is at least one more). – Jānis May 22 '12 at 13:43
  • 1
    @Janis your security comments are legitimate. I don't agree with the vague "not very efficient" claim. – Aaron Bertrand May 22 '12 at 13:49
  • @Buckley I strongly recommend using `sp_executesql` as opposed to `EXEC()`... https://sqlblog.org/blogs/aaron_bertrand/archive/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp-executesql.aspx – Aaron Bertrand May 22 '12 at 13:51
  • @buckley well if you use Exec (..), then no, unless the query is the same- so the same parameter values, white spaces.. – Jānis May 22 '12 at 13:53
  • @AaronBertrand "not very efficient" was about Exec. Great article on subject can be found here http://www.sommarskog.se/dynamic_sql.html#queryplans – Jānis May 22 '12 at 13:58
  • @Aaron Bertrand In this case the OP wants to parameterize an operator (note that he calls it an operand but the correct term is operator). In that case sp_executesql won't help. As for the parameterization that the optimizer performs it also happens for exec's due to the "auto parameterizetion" when constructing an execution plan. But I agree that sp_executesql should be preferred as it offers everything that exec can and more AFAIK. But in this *specific* case I found Exec a bit less typing and didn't see any benefits of using sp_executesql – buckley May 22 '12 at 13:58
  • It's more about general best practices IMHO. Best practices trump typing in my books. Remember that not all readers of your answer will have the OP's very specific situation. – Aaron Bertrand May 22 '12 at 13:59
  • @buckley for sp_executesql first parameter is nvarchar- so, you can make query as sqlfinal = sql + operator + sql2 ;) – Jānis May 22 '12 at 14:02
  • Okay, sp_executesql is The Pit of Success :) I'll update my answer. Still, it's good we had the discussion as it can prevent less experienced coders use EXEC and be ingorant of its dangers. @Aaron Can we say that Exec should be considered obsolete? should it be still in the product other than for historical reasons as I presume exec was introduced before sp_executesql? – buckley May 22 '12 at 14:10
  • I consider `EXEC()` obsolete. Microsoft doesn't. But that doesn't mean we should keep using it in code samples in our answers. :-) – Aaron Bertrand May 22 '12 at 14:14
  • You can exec('') as OtherUser, what you cant do with sp_executesql and you can exec('') at linkedServer.. – Jānis May 22 '12 at 14:23
  • Thanks for the clarification Jānis. Didn't have to support those scenario's in my short life so I'll start to execute_Sql from now one and educate others to stay away from EXEC – buckley May 22 '12 at 14:25
  • I tried this on the SQL dataset on the asp.net application but it failed. Not supporting EXEC. – asembereng May 22 '12 at 15:47
  • @asembereng Could you give us some more context where this sql is called even a small piece of code that isolates the problem? You can update your question. I'll be sure to take a look at it – buckley May 22 '12 at 16:22
  • @asembereng Following up, did you solve the problem? See previous comment if not. – buckley May 23 '12 at 11:15