SQL SERVER dynamic Query with Like Statement for null values I'm trying to execute a SQL dynamic query with nullable parameters using LIKE. If the parameter has a value, the query is successfully executed. But if parameter is NULL, query breaks returning only "Command(s) completed successfully" without any values. 

Here is my query:

DECLARE  @articleNo VARCHAR(100), @query varchar(8000)

 SET @articleNo = NULL

SET @query = 
 'SELECT AT.Id AS ArticleId, AT.[Description] AS ArticleName, AT.ArticleNo, AG.Id AS ArticleGroupId, AG.Name AS ArticleGroup, St.Quantity AS Stock, AB.[Key] AS Attribute, AB.[Value] AS AttributeVal   
    FROM [dbo].[Articles] AS AT
    INNER JOIN [dbo].[ArticleGroups] AS AG ON AT.ArticleGroupId = AG.Id
    LEFT OUTER JOIN [dbo].[ArticleAttributes] AS AB ON AT.Id = AB.ArticleId
    LEFT OUTER JOIN (SELECT ArticleId, SUM(Quantity) AS Quantity FROM [dbo].[StockEvent] GROUP BY ArticleId) AS ST ON AT.Id = ST.ArticleId
    WHERE 
    ('''+ CAST((@articleNo) AS varchar) +''' = '''' OR AT.ArticleNo LIKE ''%' + CAST((@articleNo) AS varchar) + '%'')'

    EXEC (@query)
Try This

DECLARE @articleNo VARCHAR(100)
,@query VARCHAR(8000)

SET @articleNo = NULL
SET @articleNo = COALESCE(@articleNo, '') -- SET to a blank value if NULL, you can't concatenate a NULL value into the string like you were doing.
SET @query = 
'SELECT AT.Id AS ArticleId, AT.[Description] AS ArticleName, AT.ArticleNo, 
AG.Id AS ArticleGroupId, AG.Name AS ArticleGroup, St.Quantity AS Stock, AB.[Key] AS Attribute, AB.[Value] AS AttributeVal   
FROM [dbo].[Articles] AS AT
INNER JOIN [dbo].[ArticleGroups] AS AG ON AT.ArticleGroupId = AG.Id
LEFT OUTER JOIN [dbo].[ArticleAttributes] AS AB ON AT.Id = AB.ArticleId
LEFT OUTER JOIN (SELECT ArticleId, SUM(Quantity) AS Quantity FROM [dbo]. 
[StockEvent] GROUP BY ArticleId) AS ST ON AT.Id = ST.ArticleId
WHERE 
       (@articleNo = ''''   OR AT.ArticleNo LIKE ''%@articleNo%'') '
--DON't use EXEC (@query), you want to make sure dynamic sql is parameterized


EXEC sp_executesql @query
,@params = N'@articleNo VARCHAR(100)'
,@articleNo = @articleNo