TSQL变量和游标范围

我想在条件变量上创建一个游标，下面是一个简短的片段：

DECLARE
  @my_var VARCHAR(8)
SET @my_var = (some value is obtained and set for @myvar)
DECLARE my_cursor CURSOR FOR
  SELECT
    my_cols
  FROM
    my_table
  WHERE
    some_col < @my_var

在声明游标之前，我需要从变量@my_var获得条件，但是Sybase抱怨DECLARE CURSOR必须在它自己的批中。但是，如果我将GO放在DECLARE CURSOR之前，那么变量@my_var将超出范围。

如何解决此范围界定问题？

导致问题的代码部分：

DECLARE
  @threshold_date VARCHAR(8),
  @retain_period INT
SET @retain_period =
(
  SELECT
    RETAIN_PERIOD
  FROM
    ARCHIVE_RETAIN_PERIOD
)
IF(@retain_period > 0)
  SET @retain_period = @retain_period * -1
SET @threshold_date = CONVERT(VARCHAR(8), DATEADD(DAY, @retain_period, GETDATE()), 112)
DECLARE archive_cursor CURSOR FOR
  SELECT
    TIMESTAMP,
    TIMESTAMP_GRP,
    CL_ORDER_ID,
    CL_ORDER_GROUP_ID,
    CL_PARENT_CHILD,
    CL_PARENT_ORDER_ID,
    CL_NUM_CHILDREN,
    CL_PRIMARY_STATE,
    CL_SECONDARY_STATE,
    CL_PENDING_CHANGE,
    CL_QUANTITY_FILLED,
    CL_QUANTITY_FILLED_HOUSE,
    CL_QUANTITY_FILLED_BROKER,
    CL_QUANTITY_FILLED_CLIENT,
    CL_QUANTITY_REMAINING,
    CL_NUM_EXECUTIONS,
    CL_CHILD_QUANTITY,
    CL_CHILD_QUANTITY_REMAINING,
    CL_GROSS_AVG_PRICE,
    CL_GROSS_AVG_PRICE_HOUSE,
    CL_GROSS_AVG_PRICE_BROKER,
    CL_QUANTITY_BOOKED,
    CL_BOOKING_PRICE,
    COMPLETION_REASON,
    COMMISSION,
    SALES_CREDIT,
    MARKUP,
    ROUTED_COUNTERPARTY_ID,
    CURRENT_EXECUTOR_ID,
    LAST_EXECUTOR_ID,
    RETAINED_EXECUTOR_ID,
    ROUTED_TO,
    RETAINED_SERVICE_ID,
    UPDATE_COUNT,
    UPDATE_USER,
    UPDATE_DATE,
    TRADER_MANAGED
  FROM
    my_db
  WHERE
    TIMESTAMP < @threshold_date
  AND
    CL_PRIMARY_STATE = "C"
GO

这个地方让我有点好奇：

IF(@retain_period > 0)
    SET @retain_period = @retain_period * -1

认为它可能需要

IF @retain_period>0 THEN
    SET @retain_period=@retain_period *-1;
END IF

这可能会让事情变得混乱，并给你错误信息。

EDIT每天学习新东西：在Sybase中，多行IF-THEN必须以两个单词的end IF结尾：

SET @retain_period =
(
  SELECT
    ABS(RETAIN_PERIOD) * -1
  FROM
    ARCHIVE_RETAIN_PERIOD
)

只是说说而已。

相关内容

最新更新

热门标签：