Regarding case when in stored procedure

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开发者_如何学C 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.

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

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)