Is there a better way?

Question

Is there a better way?

TriggerMe

SSChampion

Points: 11752
More actions
October 10, 2005 at 8:53 pm

#91754

I have the following stored proc. I basically just need to return 1 value whaich is the total depending on the EventCategory that gets passed. At the moment, it returns 1 value and NULLS for each other column. Is it possible to just return the 1 value based on the eventcategory that gets passed in?
CREATE PROC USP_CalculateEconImpactDays @MeetingID INT, @MeetingEventCategoryID CHAR(1), @DelDays INT
AS
SET NOCOUNT ON
SELECT CASE WHEN @MeetingEventCategoryID = 'L' THEN @DelDays * 175 END AS 'LocalEconImpactDaysTotal',
CASE WHEN @MeetingEventCategoryID = 'N' THEN @DelDays * 420 END AS 'NatEconImpactDaysTotal',
CASE WHEN @MeetingEventCategoryID = 'S' THEN @DelDays * 330 END AS 'StateNatEconImpactDaysTotal',
CASE WHEN @MeetingEventCategoryID = 'R' THEN @DelDays * 500 END AS 'AsiaPacNatEconImpactDaysTotal',
CASE WHEN @MeetingEventCategoryID = 'W' THEN @DelDays * 500 END AS 'InterNatEconImpactDaysTotal'
Kindest Regards,

Viewing 9 posts - 1 through 8 (of 8 total)

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

R and W seem to have the same calculation, but you are naming your output differently. If that is important, than this looks fine. Is this slow?

I wasn't born stupid - I had to study.

TriggerMe SSChampion Points: 11752 More actions · Answer 2

Its not slow! I just wanted to return 1 value(column) as opposed to 5 values (5 columns). I think it can be done with IF and ELSE IF. I'm trying that as we speak.

Kindest Regards,

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Sorry. I did not read that closely enough. (being a bit of a doooomas, I guess...)

Since you need to name each return differently, that is probably a good approach.

I wasn't born stupid - I had to study.

TriggerMe SSChampion Points: 11752 More actions · Answer 4

TriggerMe

SSChampion

Points: 11752

October 10, 2005 at 10:52 pm

#596436

Thanks Farrell.

Kindest Regards,

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 5

CREATE PROC USP_CalculateEconImpactDays @MeetingID INT, @MeetingEventCategoryID CHAR(1), @DelDays INT

AS

SET NOCOUNT ON

if @MeetingEventCategoryID = 'L'

select @DelDays * 175 AS LocalEconImpactDaysTotal

if @MeetingEventCategoryID = 'N'

select @DelDays * 420 AS NatEconImpactDaysTotal

if @MeetingEventCategoryID = 'S'

select @DelDays * 330 AS StateNatEconImpactDaysTotal

if @MeetingEventCategoryID = 'R'

select @DelDays * 500 AS AsiaPacNatEconImpactDaysTotal

if @MeetingEventCategoryID = 'W'

select @DelDays * 500 AS InterNatEconImpactDaysTotal

Joe

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

You should always have an ELSE when using IF's...

I wasn't born stupid - I had to study.

noeld SSC Guru Points: 96590 More actions · Answer 7

OR

-> Use an output parameter in your proc!

* Noel

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 8

I agree with noeld. Perhaps something like:

CREATE PROC USP_CalculateEconImpactDays ( @MeetingID INT, @MeetingEventCategoryID CHAR(1), @DelDays INT, @econimpactdaystotal INT OUTPUT ) AS SET NOCOUNT ON SET @econimpactdaystotal = CASE @MeetingEventCategoryID WHEN 'L' THEN @DelDays * 175 WHEN 'N' THEN @DelDays * 420 WHEN 'S' THEN @DelDays * 330 WHEN 'R' THEN @DelDays * 500 WHEN 'W' THEN @DelDays * 500 ELSE NULL END

This returns the computed value in the output variable @econimpactdaystotal.

Guys, feel free to correct any possible syntax errors.