I Am Getting Error If I Execute The Below Query (An Expression Of Non-Boolean Type Specified In A Context Where A Condition Is Expected, Near 'Then'.)

The error is quite clear.
Certainly you can not include a variable getting a value from the database along with data retrieval.

If I understand your scenario correctly, you want a standard profile ID but a different full name based on profile category ID. There are two approaches you can use.

1. Run two SQL queries. One for data retrieval and one for the value retrieval in the variable

2. Insert the values in a temporary table and then select from the temporary table
Example:

declare @tempTable table (profileID int, fullname varchar(100))

insert into @tempTable

select U.ProfileID,

Case when (P.ProfileCategoryID in (2546 , 2547) AND P.ProfileID=@parameter)
Then(
          SELECT DISTINCT U.fullname FROM TM_profile P
          JOIN TM_User U ON U.ProfileID= P.ProfileID
          WHERE P.ProfileCategoryID IN (2545,2544,2543) AND U.Is_Active=1)



when (P.ProfileCategoryID=2544 AND P.profileid=@parameter)
Then(
        SELECT DISTINCT U.FullName FROM TM_profile P
        JOIN TM_User U ON U.ProfileID= P.ProfileID
        WHERE P.ProfileCategoryID IN (2543) AND U.Is_Active=1)


When (P.ProfileCategoryID=2545 AND P.profileid=@parameter)
Then(
        SELECT DISTINCT U.FullName FROM TM_profile P
        JOIN TM_User U ON U.ProfileID= P.ProfileID
        WHERE P.ProfileCategoryID IN (2544,2543) AND U.Is_Active=1)



When ( P.ProfileCategoryID=2543 AND P.profileid=@parameter  )
Then(
        SELECT distinct FullName FROM TM_profile P
        join TM_User U on U.ProfileID= P.ProfileID
        WHERE P.ProfileCategoryID in (2544) and U.Is_Active=1)

END
from TM_User U
JOIN TM_profile P ON U.ProfileID= P.ProfileID
where U.TM_UserID=106

select * from @tempTable

I don't know your circumstances for the DB design but please make sure the design is correct in order to prevent further hurdles in getting the data out for reporting purposes or any other purposes if any...

Happy coding..!!

-Nayan

You cannot nest a select like that. I have no idea what you're hoping to do, but it's utterly broken.

First:

when (P.ProfileCategoryID=2544 AND P.profileid=@parameter)
Then(
        SELECT DISTINCT U.FullName FROM TM_profile P
        JOIN TM_User U ON U.ProfileID= P.ProfileID
        WHERE P.ProfileCategoryID IN (2543) AND U.Is_Active=1)

You seem to want to grab a different full name based on another id. If this is correct, it's certainly arcane and an indication that your DB is broken

Secondly, you just cannot nest a select like this. It does not work. The syntax is utterly wrong and broken. You need to post a new question, explaining your data structure ( preferably with scripts to create and populate the tables so we can help you ), and what it is you're trying to do.

This looks like you made something up at random.