How to add coulmn value addition on a condition

  • i had one table having column named as

    create table test (

    activity char(1),

    numval int

    )

    having values in this format

    activity numval

    A 10

    B 20

    C 30

    D 40

    E 50

    so now what i want if i pass a parameter from stored proc as @activity='E'

    then i should get the output as 50

    if i send a parameter from stored as @activity='D'

    then i should get output 40+50 i.e. 90

    same way i want @activity='C'

    then i should get output 30+40+50=120

    Help me out in above questino

    vijay verma


    vijay verma

  • I am confused are you saying you want to submit multiple values and get the sum? If so then do your procedure like so.

    CREATE PROC ip_SumAct

    @activity VARCHAR(20)

    AS

    SET NOCOUNT ON

    CREATE #tmpTbl (

    activity VARCHAR(1)

    )

    WHILE CHARINDEX(',', @activity) --Loop thru the submitted values to make sure we can use them.

    BEGIN

    INSERT INTO #tmpTbl (activity) VALUES (LEFT(@activity, 1)

    SET @activity = RIGHT(@activity, LEN(activity) - CHARINDEX(',', @activity))

    END

    IF LEN(@activity) > 0 --Make sure got all data.

    BEGIN

    INSERT INTO #tmpTbl (activity) VALUES (@activity)

    END

    SELECT sum(numval) AS totalsum FROM test WHERE activity IN (SELECT activity FROM #tmpTbl)

    DROP #tmpTbl

    GO

    Then all you have to do is submit like so.

    ip_SumAct 'E'

    will return just E's value

    ip_SumAct 'E,D,C' or ip_SumAct 'E,D,C,'

    will return the total for E+D+C

    hope this helps.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • To create the procedure:-

    CREATE PROCEDURE spsl_Test

    @Activitychar(1)

    ,@Numvalint output

    AS

    select

    @Numval = sum(Numval)

    from

    test

    where

    ascii(Activity) >= ascii(@Activity)

    To execute:-

    EXEC spsl_Test @Activity, @Numval OUTPUT

    Regards,

    Andy Jones

    .

Viewing 3 posts - 1 through 2 (of 2 total)

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