Board index » delphi » Problem when run SQL stored proc (with cursor) from Delphi

Problem when run SQL stored proc (with cursor) from Delphi

I have a stored proc on SQL as below:

*******************************************
CREATE   PROCEDURE pPairTradesEstimate AS

DECLARE
@PairID BIGINT,
@Tag INT,
@StartDate DATETIME,
@EndDate   DATETIME

DECLARE csr1 CURSOR FOR
 SELECT iGrandSymbolPK,
  iTag,
  dtStartDate,
  dtEndDate
 FROM tmpPairTrades

OPEN csr1
WHILE (0=0)
 BEGIN
   FETCH NEXT FROM csr1 INTO @PairID, @Tag, @StartDate, @EndDate

 EXEC pPairStatDetail1 @PairID, @Tag, @StartDate, @EndDate

 UPDATE tmpPairTrades

 SET  WL = (SELECT iWL FROM tmpPairDetail1
                         WHERE dtDate = (SELECT MIN(dtDate) FROM
tmpPairDetail1
                         WHERE iWL IS NOT NULL))

 WHERE  iGrandSymbolPK = @PairID
 AND iTag = @Tag
 AND dtStartDate = @StartDate
 AND  dtEndDate = @EndDate

 IF (@@fetch_Status <> 0 ) BREAK

 END
CLOSE csr1
DEALLOCATE csr1
*****************************************

This works fine on SQL (using Query Analyser),
however, when I try to run this stored proc form Delphi using this
procedure:

**********************************************
procedure TdmMain.RunspPairTradesEstimate;
  begin
  with spPairTradesEstimate do
    begin
    Close;
    Unprepare;
    Prepare;
    Open;
    end;
  end;
*************************************************

I got error message saying "error creating cursor handle", what's the
problem?

Thanks a lot.

Jingyi

 

Re:Problem when run SQL stored proc (with cursor) from Delphi


    Does not look like this SP is returning a result set so try ExecProc
instead of Open.

--
Sunil Furtado
New Mangalore Port Trust

Quote
Jingyi Peng <pe...@rotellacapital.com> wrote in message

news:3b56163c$1_1@dnews...

Other Threads