Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    20

    Unanswered: case / if statement in SPROC

    CREATE PROCEDURE sp_get_data

    (
    @station_no varchar(255) = NULL,
    @meas_compare_val int(255) = NULL,
    @meas_compare_type varchar(255) = NULL
    )

    AS

    BEGIN


    SET @station_list = COALESCE(RTRIM(LTRIM(@station_list)),'')

    select

    station_number, meas_value

    from

    v_extract

    where

    ((LEN(@station_no) = 0) OR (station_number IN (SELECT * FROM udf_SplitStrings(@station_no, ',')))) AND

    --all optional values are (LT, GT, EQ, LTEQ, GTEQ, NEQ)

    if meas_compare_type == 'GT' Then

    meas_value > @meas_compare_val

    if meas_compare_type == 'LTEQ' then

    meas_valu <= @meas_compare_val

    IF @meas_compare_type = NULL

    --ignore this statement alltogether!!!!???????



    IF @meas_compare_type = ''


    order by station_number

    END

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am not sure what problem you are having this might solve a couple of them or cause a couple more...
    Code:
    CREATE PROCEDURE get_data
    
    (
    @station_list VARCHAR(255),
    @station_no varchar(255) = NULL,
    @meas_compare_val int(255) = NULL,
    @meas_compare_type varchar(255) = NULL
    )
    
    AS
    
    BEGIN
    
    SET @station_list = COALESCE(RTRIM(LTRIM(@station_list)),'')
    
    select station_number, meas_value
    from v_extract 
    where ((LEN(@station_no) = 0) OR (station_number IN (SELECT * FROM udf_SplitStrings(@station_no, ',')))) 
    AND
    --all optional values are (LT, GT, EQ, LTEQ, GTEQ, NEQ)
    ((meas_compare_type = 'GT' AND meas_value > @meas_compare_val)
    OR
    (meas_compare_type = 'LTEQ' AND meas_valu <= @meas_compare_val)
    OR
    (@meas_compare_type is NULL)
    OR
    --ignore this statement alltogether!!!!???????
    (@meas_compare_type = ''))
    order by station_number
    
    END
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2005
    Posts
    20
    this is beautiful

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it's not but you are for saying so.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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