Difference b/w Custom and Dynamic SQL

, ,
1

Hello evry one

i know how custom and dynamic SQLs are used in wM.But can any of you please explain me when they are used .For example

Dynamic SQL:
select * from emp where empno = ${whereclause}

here empno is dynamically substituted .But at the same time we can use a custom SQL for this

Custom SQL:
select * from emo where empno=?

here also we can substitute by giving inputs to adapate

Can you please tell me when each of these is used and the basic diff between them

thanks
Anil

2

Anil,
Just one scenario that can be taken from your exmaple in itself,where in you need to run select * statement on the same table with the same output in multiple places in your code such as

  1. select * from emp where empno=?
  2. select * from emp where empno=? and empname=?

If you use custom sql, you need create 2 adapter services, one with empno as input and the other with empno and empname as inputs.

But in case of dynamic SQL you can just write a statement in one variable like this:
select * from emp where ${whereclause}
and keep substituting ${whereclause} with “empno=1” and "empno=1 and empname=“xxx” and use only one adapter service.

This is one of them , it will be of good use if you need to update the same table based on various criteria because it gives you the flexbility to you, no need to create any fixed binding inputs.

Hope this is helpful.

3

thank you very much suren

4

You are welcome.