Dynamic if statment

[Highlander]

I need to write a dynamic if statment as follows.
I have a table with about 50 columns and a part of the procedure is:

if cursor1.col1 > val then...

Now, the "if" statment is always the same the only diferent is that cursor1.col1 might be cursor1.col2, cursor1.col3 ....etc depends on user input/parameter.

My question is, how can I do it with out writing 50 "if" statmenst, some thing like:

if corsor1.col||user_param then

User_parame is a number from 1 - 50

Thanks.

[Heath]

Since the name of the column is dynamic, you can't use any sort of early binding and would have to use dynamic SQL of some sort, via the DBMS_SQL package or the execute immediate statement.

HTH,

Heath

[Highlander]

How can I use an "IF" statment with execute immediate or DBMS_SQL?

[Heath]

I may have misunderstood your initial post on this thread, but I understood that you had some sort of procedure that had a parameter that identified which of your fifty columns whose value you wanted to check. The parameter value would be a number between 1 and 50. And that you just wanted to write a single set of logic to check that specific column's value.

This would imply a single SELECT statement to retrieve the value of that column

<font face="Courier New,Courier">
&nbsp;&nbsp;&nbsp; select col? from &lt;table&gt; where &lt;whatever&gt;;
</font>

The ? would be the number passed in by the parameter.

With that understanding, I suggested using dynamic SQL so that you could build the dynamic select statement using the one column in which you are interested. Something like:

<font face="Courier New,Courier">
&nbsp;&nbsp;&nbsp; v_SQL := "select col" || n_parameter || " from &lt;table&gt; where &lt;whatever&gt;";
</font>

Then you would use either the DBMS_SQL package or the execute immediate statement to run the query and get the selected value.

Then you can proceed with your IF logic.

<font face="Courier New,Courier">
&nbsp;&nbsp;&nbsp; IF &lt;selected value&gt; &gt; value THEN ...
</font>


Of course, all of the above may not help you a bit if that wasn't what you were asking. ;)

HTH,

Heath

[Highlander]

Thanks Heat,
I didn't use your example but you gave me another idea, to use a ref-cursor. So in a way you did help..Thanks.