Here's another option that moves the string manipulation and identification of NULLs to the SELECT statement instead of using it in a JOIN or WHERE clause where the 'SARG-ability' of the statement may be an issue. It sets blanks and NULLs to some value (in this example it's '0') and then the Where clause can exclude that value. This option would need to be tested against other options of course.

SELECT

[Desc]

,[Num]

,[Batch]

,[CodeId]

,[Type]

,[Id]

FROM

(

SELECT DISTINCT

CD.[Desc] AS [Desc]

,SD.[Num] AS [Num]

,SD.[Batch] AS [Batch]

,CD.[CodeId] AS [CodeId]

,SD.[Type] AS [Type]

,CH.[Id] AS [Id]

,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code]

FROM

Table1 SD

JOIN

Table2 CH

ON CH.Num = SD.Num

AND CH.Batch = @Batch

AND CH.Flag = 0

AND SD.Batch = @Batch

AND SD.Flag = 0

JOIN

Table3 M

ON M.Code = SD.Code

AND (Map = 1

OR Map = @Map)

AND CH.Date1 BETWEEN M.Date2 AND M.Date3

JOIN

Table4 CD

ON M.CodeId = CD.CodeId

) AS Result

WHERE

[Code] <> '0'