At 11:38 AM 4/07/2008, you wrote:

>Hi
>
>I have a TIBOQuery with an SQL like
>
>SELECT *
>FROM MyStoredproc(:ItemRef)
>
>Where I pass in a reference (Query.ParamByName('ItemRef').AsInteger :=
>234) an a result is returned. Works great.
>However the stored procedure is designed so that if a NULL is passed in
>totals for all items are returned. In SQL I just write in NULL. how do
>I achieve this in code (without changing the SQL for the Query and
>having to reprepare etc)?
>
>Im sure this is simple :)

First of all, you don't have to "reprepare etc" to pass a different parameter value. All you need to do is figure out how your application recognises NULL - and this entirely depends on how you're getting the values for Query.ParamByName('ItemRef').AsInteger. I'm sure you don't actually assign it the way you showed above. You must have variable or property, not something hard-coded....e.g.

Query.ParamByName('ItemRef').AsInteger := SomeIntegerVar;

Decide on some value for SomeIntegerVar that's not ever going to be used as an input value, say -99 if your input is always positive. Then, in your BeforeOpen, assign it like this:

if SomeIntegerVar >= 0 then
Query.ParamByName('ItemRef').AsInteger := SomeIntegerVar
else
Query.ParamByName('ItemRef').Clear;

Helen