SQL Server Performance Forum – Threads Archive
help withstored procedure !! if else construct!
IF @GPA100 IS NULL THENBEGIN @CUMUL = 0 END
ELSE
BEGIN
IF @GPA200 IS NULL
BEGIN SET @CUMUL = @GPA100
END
ELSE
BEGIN
IF @GPA200 IS NOT NULL AND GPS300 IS NULL
BEGIN SET @CUMUL= (@GPA200 [email protected])/2
END
ELSE
BEGIN
IF @GPA300 IS NOT NULL AND @GPA400 IS NULL
BEGIN SET @CUMUL= (((@GPA200 + @GPA100)/2) + @GPA300)/2
END
ELSE
BEGIN
IF @GPA400 IS NOT NULL AND @GPA500 IS NULL
BEGIN SET @CUMUL= ((((@GPA200 + @GPA100)/2) + @GPA300)/2) + @GPA400)/2
END
ELSE
BEGIN
IF @GPA500 IS NOT NULL AND @GPA500 IS NULL
BEGIN SET @CUMUL = (((((@GPA200 + @GPA100)/2) + @GPA300)/2) + @GPA400)/2) + @GPA500)/2
END
ELSE
BEGIN
IF @GPA600 IS NOT NULL
BEGIN @CUMUL = ((((((@GPA200 + @GPA100)/2) + @GPA300)/2) + @GPA400)/2) + @GPA500)/2) + @GPA600)/2 END anybody help me out dis code is killin me .
all variables declared and assigned values
You are missing 2 SETs and 6 ENDs at the bottom. And there is no THEN in IF..ELSE. IF @GPA100 IS NULL THEN
BEGIN
SET @CUMUL = 0
END
ELSE
BEGIN
IF @GPA200 IS NULL
BEGIN
SET @CUMUL = @GPA100
END
ELSE
BEGIN
IF @GPA200 IS NOT NULL AND GPS300 IS NULL
BEGIN
SET @CUMUL= (@GPA200 [email protected])/2
END
ELSE
BEGIN
IF @GPA300 IS NOT NULL AND @GPA400 IS NULL
BEGIN
SET @CUMUL= (((@GPA200 + @GPA100)/2) + @GPA300)/2
END
ELSE
BEGIN
IF @GPA400 IS NOT NULL AND @GPA500 IS NULL
BEGIN
SET @CUMUL= ((((@GPA200 + @GPA100)/2) + @GPA300)/2) + @GPA400)/2
END
ELSE
BEGIN
IF @GPA500 IS NOT NULL AND @GPA500 IS NULL
BEGIN
SET @CUMUL = (((((@GPA200 + @GPA100)/2) + @GPA300)/2) + @GPA400)/2) + @GPA500)/2
END
ELSE
BEGIN
IF @GPA600 IS NOT NULL
BEGIN
SET @CUMUL = ((((((@GPA200 + @GPA100)/2) + @GPA300)/2) + @GPA400)/2) + @GPA500)/2) + @GPA600)/2 END
END
END
END
END
END
END Keith Payne
Technical Marketing Solutions
www.tms-us.com
Hi,
Simple logic is when you use IF with begin and end clause write them for all if…else and it’s without THEN. simply…. IF @variable1=0
BEGIN
SET @variable2=0
END
ELSE
BEGIN
SET @variable2=1
END
Modify your code as suggested above
Where are you using this code and what are you trying to do? Madhivanan Failing to plan is Planning to fail
thank u very much for the suggestion.
please pardon my naivete just got introduced to the lingo of t-sql!! i am actually using it to produce a report dat sums up the grade point average (GPA)of students in a school. and some of the results entry contain null values so my dilemma and dats what brings about this rather untidy if…else construct any other suggestions on how to tackle dis problem are warmly welcomed thank u
With the help of ISNULL function you can minimise the code eg IF @GPA200 IS NOT NULL AND GPS300 IS NULL
BEGIN SET @CUMUL= (@GPA200 [email protected])/2
END can be IF GPS300 IS NULL
BEGIN SET @CUMUL= (ISNULL(@GPA200,0) [email protected])/2
END
Madhivanan Failing to plan is Planning to fail
Can you share the table structure that you are pulling the actual grades from, that you are using to populate your @GPA variables? I think everyone is trying to get you to consider just using the AVG function that will do what you are trying to do directly in a select clause.
select Students.Name, AVG(ISNULL(Scores.TestScore,0))
from Students inner join Scores on Scores.StudentID = Students.StudentID
group by Students.Name
* It is important to use the ISNULL inside the AVG function in this case since you have NULL values for the scores instead of zeros as defaults. Otherwise the AVG function will simply exclude the score and take an average for only those with values.
]]>