Regarding case when in stored procedure

Question

hi guys, I have following query.

 declare @Prm_CourseId int
declare @Prm_SpecializationId int
set @Prm_CourseId=5
set @Prm_SpecializationId=0
declare @WhrStr varchar(500)


set @WhrStr =  case @Prm_CourseId
       when 0 then
        'e.CourseId is null or e.CourseId is not  null'
       when -1 then
        'e.CourseId is null or e.CourseId is not  null'
       when isnull(@Prm_CourseId,0)  then
         'e.CourseId is null or e.CourseId is not  null'
       else
         'e.CourseId= '+Convert(varchar,@Prm_CourseId)

        end
set @WhrStr  = case @Prm_SpecializationId
       when 0 then
        'e.SpecializationId is null or e.SpecializationId is not  null'
       when -1 then
        'e.SpecializationId is null or e.SpecializationId is not  null'
       when isnull(@Prm_SpecializationId,0)  then
         'e.SpecializationId is null or e.SpecializationId is not  null'
       else
         'e.SpecializationId= '+Convert(varchar,@ Prm_SpecializationId)
       end
print @WhrStr
    exec(
      'select f.EnquiryID,
      e.[Name],
      f.AttendedBy,
      f.Remarks,
      f.CreatedDate
      from STD_FollowUp f
       inner join
        STD_Enquiry e
         on f.EnquiryId=e.EnquiryId
      where'+' '+@WhrStr
    )

here the problem is i want to get result in 1st and 2nd case statement in @WhrStr .But the value coming in WhrStr is 'e.SpecializationId is null or e.SpecializationId is not null' means 1st is overwritten by second.Is there anyway to append second to first.

Answer 1

This won't work in several levels.

• You can't append a string to a query. It's either all dynamic or all not.

• The CASE WHEN bit needs to be in string delimiters "...THEN '(e.CourseId is null or e.CourseId is not null)'...

• "e.CourseId is null or e.CourseId is not null" is the same "always give me data"

All you need is:

select f.EnquiryID,
    e.[Name],
    f.AttendedBy,
    f.Remarks,
    f.CreatedDate
from STD_FollowUp f
            inner join
                    STD_Enquiry e
                    on f.EnquiryId=e.EnquiryId

Answer 2

if (isnull(@Prm_CourseId, 0) = 0 or isnull(@Prm_CourseID, 0) = -1)
    set @Prm_CourseId = null

if (isnull(@Prm_SpecializationId, 0) = 0 or isnull(@Prm_SpecializationId, 0) = -1)
    set @Prm_SpecializationId = null

select f.EnquiryID,
e.[Name],
f.AttendedBy,
f.Remarks,
f.CreatedDate
from STD_FollowUp f
inner join
STD_Enquiry e
on f.EnquiryId=e.EnquiryId
where (@Prm_CourseId is null or e.CourseId = @Prm_CourseId)
or (@Prm_SpecializationId is null or e.SpecializationId = @Prm_SpecializationId)