Devart Forums

Hi,
i m using parameters.refresh method for clear parameters values in the ADO Stored Proc.
But i don't using this method in TMSStoredProc.
How can i clear all parameter values by code.
i need this method in the report form.
i have a lot of criteria editbox and one search button.
i m setting parameters values each editbox status(filled or not filled)

example:
tmsstoredproc.close;

if eitemnumber.edit''
tmsstoredproc.params.parambyname('@itemnumber').value := eitemnumber.text;

if ..... etc.
......
and
tmsstoredproc.open;
--------------------------
Thanks for your answer.

You can re-assign a name of procedure:

tmsstoredproc.StoredProcName := '';
tmsstoredproc.StoredProcName := 'QQQ';

Pay attention that this sequence the same as ADOStoredProc.parameters.Refresh requires access to the server.

From the point of view of performance, it would be correct to drop properties of all parameters in the loop.

i dont understand your comments about performence.
Can you explain exactly?

i need this parameters refresh event for dynamic search.
for example
my ms sql procedure:
-----------------------------------
create procedure spSEARCH
@PARAM1 int=-1
@PARAM2 varchar(10)=''
as
declare @sql varchar(100)

set @sql='select * from mytable where mytableid>0'

if @PARAM1-1
set @sql=@sql+' and field1='''+convert(varchar,@PARAM1)+''''

if @PARAM2''
set @sql=@sql+' and field2='''+@PARAM2+''''

exec(@sql)
-----------------------------------


and my searchform searchbutton onclick method
------------------------------------
tmsstoredproc.Close;
tmsstoredproc.storedprocname := ''
tmsstoredproc.storedprocname := 'spSEARCH'
if editbox.text'' then
tmsstoredproc.params.parambyname('@PARAM1').value := strtoint(editbox.Text);
if editbox2.Text''
tmsstoredproc.params.parambyname('@PARAM2').value := editbox2.Text;

tmsstoredproc.open;

------------------------------------

some time i filling editbox.text
some time i filling editbox2.text
i m setting param values these conditions.
but i have a problem this method:

sdac storedproc always sending null value for unsetted(not gived any values) parameters. And i want dont send any value this unsetted parameters to ms sql with NULL values.

How can I do this wish?

> i dont understand your comments about performence.
> Can you explain exactly?

On changing the name of the procedure (tmsstoredproc.storedprocname) it is required to send a request to the server to determine number and type of parameters. Certainly, it takes some time. This procedure makes sense only if the declaration (list and type of parameters) of spSEARCH changed.

> How can I do this wish?

Thank you very much.