Post reply

help needed in SQL server stored procedure

  • May 19, 2010 at 9:33 am

    #1169515

    I am sorry, i have to write only procedures, i dont have any rights to change it.. 🙂

    i gotta to try of the SP what u have written before as like this

    BEGIN

    select @lastQuartRewardState =case

    when claim_date is null then 'Present'

    when claim_date is not null and claim_type = 'Bank' then 'Banked'

    when claim_date is not null and claim_type = 'Claim' then 'Claimed'

    else 'Absent'

    END

    from earned_transaction

    where year = @prevQuartYear AND et.quarter = @prevQuarter

    AND et.msisdn = @msisdn AND (et.lapsed is null OR et.lapsed = 0)

    But i am sorry it dint work, it says Incorrect syntax near 'END'

    and also if see one of the replies in this post on first page he says like this

    "I'm not sure if I get it right, but if OP adds a condition that doesn't return any result then the 'else' condition will never be executed."

    Is that also is considered in to this solution?

  • May 19, 2010 at 9:50 am

    #1169534

    Which line is the error on? You should have two END statements, one to match the BEGIN, and another to match the CASE. I don't see the second END clause, so I don't know what code is before it. Most likely, there is a syntax error or incomplete statment right before that second END.

  • May 19, 2010 at 9:55 am

    #1169541

    yeah it works adding an END statement it works..

    Thanks

  • May 19, 2010 at 9:56 am

    #1169546

    Also, you are erroneously using the table alias "et", without actually declaring it. You need to do one of the following:

    1. Declare et: "FROM earned_transaction AS et"

    2. Use the full table name: "AND earned_transaction.quarter = @prevQuarter"

    or 3. Don't specify a table at all: "AND quarter = @prevQuarter"

  • May 19, 2010 at 10:03 am

    #1169555

    K thanks, but there seems to be problem, i tested this query, it never goes in to else 'Absent' part, means when there is no transaction it has to go there, bit it doesnt..

    Please help

  • May 19, 2010 at 10:06 am

    #1169564

    Is it returning NULL instead? You could:

    SET @lastQuartRewardState = ISNULL(@lastQuartRewardState, 'Absent')

    ... at the very end.

  • May 19, 2010 at 10:21 am

    #1169583

    Yes it works, thanks

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply