how to set default value in a output parameter

Question

in my following query i want to set 0, 0, 0 in @TMarks, @OMarks, @Percentage respectively if the select statement used with them returns nothing

    create procedure [dbo].[TestRecordSelectMInfo]
@GRNo varchar(4),
@SessionId numeric(1),
@TestTypeId numeric(1),
@TMarks int output,
@OMarks numeric(4) output,
@Percentage numeric(4) output,
@Grade varchar(4) output
as 
begin
SELECT Subjects.Subject, Marks.TotalMarks, Marks.PassingMarks, TestRecord.Marks, Result = case when TestRecord.Marks = 'A' then 'A' else case when cast(TestRecord.Marks as numeric) < Marks.PassingMarks then 'F' else 'P' end end FROM Subjects INNER JOIN Marks ON Subjects.SubjectId = Marks.SubjectId INNER JOIN TestRecord ON Subjects.SubjectId = TestRecord.SubjectId AND Marks.TestTypeId = TestRecord.TestTypeId where TestRecord.SessionId = @SessionId and TestRecord.TestTypeId = @TestTypeId and TestRecord.GRNo = @GRno
set @TMarks = (select sum(Marks.TotalMarks) from Marks inner join TestRecord on Marks.TestTypeId = TestRecord.TestTypeId and Marks.SubjectId = TestRecord.SubjectId where TestRecord.SessionId = @SessionId and TestRecord.TestTypeId = @TestTypeId and TestRecord.GRNo = @GRNo and TestRecord.Marks <> 'A' and cast(TestRecord.Marks as numeric) > Marks.PassingMarks )
set @OMarks = (select sum(cast(TestRecord.Marks as numeric)) from Marks inner join TestRecord on Marks.TestTypeId = TestRecord.TestTypeId and Marks.SubjectId = TestRecord.SubjectId where TestRecord.SessionId = @SessionId and TestRecord.TestTypeId = @TestTypeId and TestRecord.GRNo = @GRNo and TestRecord.Marks <> 'A' and cast(TestRecord.Marks as numeric) > Marks.PassingMarks)
set @Percentage = @OMarks / @TMarks * 100;
set @Grade = case
when @Percentage < 50
then
'NIL'
else
case 
when @Percentage < 60
then
'C'
else
case
when @Percentage < 70
then
'B'
else
case
when @Percentage < 80
then
'A'
else
case
when @Percentage <= 100
then
'A+'
else
'FAIL'
end
end
end
end
end
end
GO

Andomar · Accepted Answer · 2012-04-21 12:01:33Z

2

EDIT: An aggregate without a group by returns null for an empty set. You could work around this with insull:

select  @TMarks = IsNull(sum(Marks.TotalMarks),0)
from    Marks  
inner join TestRecord
  on Marks.TestTypeId = TestRecord.TestTypeId 
  and Marks.SubjectId = TestRecord.SubjectId 
where TestRecord.SessionId = @SessionId 
  and TestRecord.TestTypeId = @TestTypeId 
  and TestRecord.GRNo = @GRNo 
  and TestRecord.Marks <> 'A' 
  and cast(TestRecord.Marks as numeric) > Marks.PassingMarks

edited Apr 21, 2012 at 12:01

answered Apr 21, 2012 at 11:52

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andomar Over a year ago

Ok, more detailed example now

Andomar Over a year ago

You're right, it's an aggregate without a group by. Answer updated.

kashif Over a year ago

Thanks Andomar it worked. I'm now getting zero if the select statement is returning null. Thanks really very much

Collectives™ on Stack Overflow

how to set default value in a output parameter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related