0

In a simple scalar function, I got several "IFs" separated each of them with a unique condition.

Is there any way that I can check which condition was satisfied without having to change the function to log my conditions?

IF ( SOME CONDITION) RETURN 1;

IF ( SOME OTHER CONDITION ) RETURN 1;

What I need to do is check if the condition met was the first or the second.

EDIT: I know this looks terrible but I also need to get the SQL code that made the condition work. I have no idea how this is possible but I'm hoping any solution.

  • Are you able to return a different code per condition? That would be the easiest solution. – World Wide DBA Jan 16 '18 at 20:04
  • It is! I had this idea but I was expecting something different since I have +500 conditions. Changing all them would require a lot of time. – Jelther Gonçalves Jan 16 '18 at 20:58
  • It is! I had this idea but I was expecting something different since I have +500 conditions. Changing all them would require a lot of time. – Jelther Gonçalves Jan 16 '18 at 20:58
  • If you have 500+ checks within a single function then I would recommend re-engineering some if not all of the call-stack. If you cannot do that, then you are left with returning a different code per check - which then you have to handle in the calling layer. So then you have 500 checks for a return code. Its a no-win scenario... – World Wide DBA Jan 16 '18 at 21:03
1

Turn your function into an in-line table-valued function, and then pass your extra info in other columns. You can easily call it using CROSS APPLY in your FROM clause, and then use the result in WHERE predicates, SELECT lists, wherever you like. Plus being an ITVF, it’ll perform much better.

| improve this answer | |
  • Unfortunately, I don't have this option. All I can do is change the where condition or alter the function definition. :( – Jelther Gonçalves Jan 16 '18 at 20:59
  • And the function cannot be changed to other type than scalar function. – Jelther Gonçalves Jan 16 '18 at 21:02
  • You’re calling a scalar function in a WHERE clause! Oh, I strongly recommend you change that. With your current restrictions, I’m not sure you can achieve what you want without having a second function to indicate which condition was satisfied. – Rob Farley Jan 17 '18 at 3:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.