case statement sub query need to return multiple values Below query need to return multiple values 

select  

CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
THEN (SELECT StationeryClientProject.ImpFile from StationeryClientProject 
      WHERE StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id)
ELSE (SELECT Stationery.ImpFile from Stationery 
      WHERE Stationery.Stationery_Id = [Order].Stationery_Id)
END AS ImpFile

FROM [Order]
LEFT OUTER JOIN ClientProject ON ClientProject.ClientProject_Id = [Order].ClientProject_Id
LEFT OUTER JOIN StationeryClientProject ON StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id 
LEFT OUTER JOIN Stationery ON Stationery.Stationery_Id = [Order].Stationery_Id


but it was showing up an error


  Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1
  value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= ,
  >, >= or when the subquery is used as an expression.


Not sure how to handle it. any help would be highly appreciated
This should work:

select  

CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
    THEN StationeryClientProject.ImpFile
ELSE Stationery.ImpFile
END AS ImpFile

FROM [Order]
    LEFT OUTER JOIN ClientProject ON   ClientProject.ClientProject_Id = [Order].ClientProject_Id
    LEFT OUTER JOIN StationeryClientProject ON StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id 
    LEFT OUTER JOIN Stationery ON Stationery.Stationery_Id = [Order].Stationery_Id


The error message is caused by multiple rows being returned inside the case statement.  Since you are already returning the required rows in your FROM clause, you can simply reference the required columns in the case statement.