SELECT [Theme].[PK_Theme], [Theme].[Name], [ThemeType].[Type]
FROM [Theme]
LEFT OUTER JOIN [ThemeType]
ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType]
JOIN [ProductTheme] ON [ProductTheme].[ThemeId]=[Theme].[PK_Theme]
WHERE ProductTheme.ProductID LIKE @productParam
AND ProductTheme.ThemeId = Theme.PK_Theme
AND COALESCE([THEME].[THEMETYPEID], 'null') LIKE @assignedParam
GROUP BY [Theme].[Name], [ThemeType].[Type], [Theme].[PK_Theme]
ORDER BY CASE WHEN [ThemeType].[Type] IS NULL THEN 0 ELSE 1 END, [Theme].[Name]

Conversion failed when converting the varchar value 'NULL' to data type int.

AND COALESCE([THEME].[THEMETYPEID], 'null') LIKE @assignedParam

AND COALESCE([THEME].[THEMETYPEID], NULL) LIKE @assignedParam

AND COALESCE([THEME].[THEMETYPEID], 0) LIKE @assignedParam

SELECT  [Theme].[PK_Theme] ,
                [Theme].[Name] ,
                [ThemeType].[Type]
        FROM    [Theme]
                LEFT OUTER JOIN [ThemeType] ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType]
                JOIN [ProductTheme] ON [ProductTheme].[ThemeId] = [Theme].[PK_Theme]
        WHERE   ProductTheme.ProductID LIKE @productParam
                AND ProductTheme.ThemeId = Theme.PK_Theme
                AND 1 = CASE WHEN [THEME].[THEMETYPEID] IS NULL THEN 1
                             WHEN [THEME].[THEMETYPEID] LIKE @param THEN 1
                        END
        GROUP BY [Theme].[Name] ,
                [ThemeType].[Type] ,
                [Theme].[PK_Theme]
        ORDER BY CASE WHEN [ThemeType].[Type] IS NULL THEN 0
                      ELSE 1
                 END ,
                [Theme].[Name]

SELECT [Theme].[PK_Theme], [Theme].[Name], [ThemeType].[Type]
FROM [Theme]
LEFT OUTER JOIN [ThemeType]
ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType]
JOIN [ProductTheme] ON [ProductTheme].[ThemeId]=[Theme].[PK_Theme]
WHERE ProductTheme.ProductID LIKE @productParam
AND ProductTheme.ThemeId = Theme.PK_Theme AND [Theme].[ThemeTypeId] IS NOT NULL
AND COALESCE([THEME].[THEMETYPEID], 'null') LIKE @assignedParam
GROUP BY [Theme].[Name], [ThemeType].[Type], [Theme].[PK_Theme]
ORDER BY CASE WHEN [ThemeType].[Type] IS NULL THEN 0 ELSE 1 END, [Theme].[Name]