🚀 go-pugleaf

Hi there,
i've a problem concerning stored procedures with cppbuilder 6.0,
dbexpress and mssql server 2000.

for testing i created the following stored procedure on sql server:

CREATE PROCEDURE sp_CountPatients
@result int OUTPUT,
@p int
AS
SELECT @result = COUNT(*) FROM Patient
WHERE LastName LIKE '%' + CAST(@p AS varchar) + '%'
RETURN
GO

the code to test this procedure:

    TSQLStoredProc *sp = new TSQLStoredProc(this);
    sp->SQLConnection = CommonDataModule->SQLConnection;
    sp->StoredProcName = "sp_CountPatients";
    sp->Params->ParamByName("p")->AsInteger = 1 ;
    sp->ExecProc();
    int t1 = sp->ParamByName("result")->AsInteger;
    return t1;

as i realized poor performance by using this stored procedure i did a
trace with SQL Profiler which gave me the following result:

RowNumber   EventClass  TextData
----------- ----------- -------------------------
1           12          DECLARE @SCH CHAR(200) ...
2           12          DECLARE @SCH CHAR(200) ...
3           10          declare @P1 int set @P1=1 ...

The statment in Row 1 and 2 is exactly the same, is very lage (66
Lines) and is executed every time before the stored procedure is
executed. The result of this statement gives me the parameters of the
stored procedure with informations about data type and so on.

I guessed this is because default the ParamCheck Property of
TSQLStoredProcedure is set to true. I did some testing, set
sp->ParamCheck = false, so i could avoid the double execution of this
statement. But the problem is still present, every time i execute this
stored procedure, this statement is executed.

I did some testing with BDE, there is no Statement for the Parameters
executed, but i try to find a solution for dbexpress ... :)

has anyone an idea where this Statement come from and how to disable
this ?

Thanks in advance

Michael