July 11, 2002 at 7:11 am
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
July 11, 2002 at 7:59 am
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)
July 11, 2002 at 8:12 am
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