Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Red face Unanswered: Result set not available from VC

    Hi,

    I'm facing a small problem here. The below given stored procedure is an SQL stored procedure. All parameters passed to the SP are used in the where clause. This SP when run from query analyser is perfect. But when calling this from within a VC program it does not give back any results. If the last comparison of the start time is commented, it gives results in both query analyser and from within the VC program. Does anyone have any clue as to what could be wrong. The alternate solution to this is to not pass in the time parameter and to do the comparison from within the VC program.

    CREATE PROCEDURE ApcSp_GetWERuleData
    (
    @binResultId VARBINARY(10),
    @binChartId VARBINARY(10),
    @nRevision INT,
    @nChartPrmId INT,
    @szStartTime VARCHAR(20)
    )

    AS

    BEGIN


    SELECT TOP 15

    CASE
    WHEN @nChartPrmId = 1 THEN APCPDATA_trn.Mean
    WHEN @nChartPrmId = 2 THEN APCPDATA_trn.[Max]
    WHEN @nChartPrmId = 3 THEN APCPDATA_trn.[Min]
    WHEN @nChartPrmId = 4 THEN APCPDATA_trn.[StDev]
    WHEN @nChartPrmId = 5 THEN APCPDATA_trn.Range
    WHEN @nChartPrmId = 6 THEN APCPDATA_trn.Snapshot
    WHEN @nChartPrmId = 7 THEN APCPDATA_trn.Slope
    WHEN @nChartPrmId = 8 THEN APCPDATA_trn.Residual
    WHEN @nChartPrmId = 9 THEN APCPDATA_trn.Integrated
    WHEN @nChartPrmId = 10 THEN APCPDATA_trn.Duration
    WHEN @nChartPrmId = 30 THEN APCPDATA_trn.RowData
    WHEN @nChartPrmId = 31 THEN APCPDATA_trn.RowData
    ELSE NULL
    END,
    APCPDATA_trn.AlertLevel

    FROM APCPDATA_trn

    WHERE APCPDATA_trn.ResultDataId = @binResultId
    AND APCPDATA_trn.ChartId = @binChartId
    AND APCPDATA_trn.Revision = @nRevision
    AND APCPDATA_trn.ChartPrmId = @nChartPrmId

    AND APCPDATA_trn.StartTime <= @szStartTime

    ORDER BY APCPDATA_trn.StartTime DESC

    END
    GO

  2. #2
    Join Date
    Oct 2002
    Location
    Argentina
    Posts
    72

    Re: Result set not available from VC

    Supposing that APCPDATA_trn.StartTime has the datetime or smalldatetime you must
    set the parameter type to smalldatetime or date time


    CREATE PROCEDURE ApcSp_GetWERuleData
    (
    @binResultId VARBINARY(10),
    @binChartId VARBINARY(10),
    @nRevision INT,
    @nChartPrmId INT,
    @szStartTime SMALLDATETIME


    Originally posted by superman_san
    Hi,

    I'm facing a small problem here. The below given stored procedure is an SQL stored procedure. All parameters passed to the SP are used in the where clause. This SP when run from query analyser is perfect. But when calling this from within a VC program it does not give back any results. If the last comparison of the start time is commented, it gives results in both query analyser and from within the VC program. Does anyone have any clue as to what could be wrong. The alternate solution to this is to not pass in the time parameter and to do the comparison from within the VC program.

    CREATE PROCEDURE ApcSp_GetWERuleData
    (
    @binResultId VARBINARY(10),
    @binChartId VARBINARY(10),
    @nRevision INT,
    @nChartPrmId INT,
    @szStartTime VARCHAR(20)
    )

    AS

    BEGIN


    SELECT TOP 15

    CASE
    WHEN @nChartPrmId = 1 THEN APCPDATA_trn.Mean
    WHEN @nChartPrmId = 2 THEN APCPDATA_trn.[Max]
    WHEN @nChartPrmId = 3 THEN APCPDATA_trn.[Min]
    WHEN @nChartPrmId = 4 THEN APCPDATA_trn.[StDev]
    WHEN @nChartPrmId = 5 THEN APCPDATA_trn.Range
    WHEN @nChartPrmId = 6 THEN APCPDATA_trn.Snapshot
    WHEN @nChartPrmId = 7 THEN APCPDATA_trn.Slope
    WHEN @nChartPrmId = 8 THEN APCPDATA_trn.Residual
    WHEN @nChartPrmId = 9 THEN APCPDATA_trn.Integrated
    WHEN @nChartPrmId = 10 THEN APCPDATA_trn.Duration
    WHEN @nChartPrmId = 30 THEN APCPDATA_trn.RowData
    WHEN @nChartPrmId = 31 THEN APCPDATA_trn.RowData
    ELSE NULL
    END,
    APCPDATA_trn.AlertLevel

    FROM APCPDATA_trn

    WHERE APCPDATA_trn.ResultDataId = @binResultId
    AND APCPDATA_trn.ChartId = @binChartId
    AND APCPDATA_trn.Revision = @nRevision
    AND APCPDATA_trn.ChartPrmId = @nChartPrmId

    AND APCPDATA_trn.StartTime <= @szStartTime

    ORDER BY APCPDATA_trn.StartTime DESC

    END
    GO

  3. #3
    Join Date
    Nov 2003
    Posts
    2

    Result set not available from VC

    The StartTime field is character. Besides, I'm getting correct results when run from query analyser.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •