SQL SERVER dynamic Query with Like Statement for null values

Question

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].[AT] AS AT
    INNER JOIN [dbo].[AG] AS AG ON AT.ArticleGroupId = AG.Id
    LEFT OUTER JOIN [dbo].[AAT] AS AB ON AT.Id = AB.ArticleId
    LEFT OUTER JOIN (SELECT ArticleId, SUM(Quantity) AS Quantity FROM [dbo].[SE] 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)

I think you need to use IS NULL instead of = '' for the first comparison, if I'm understanding you correctly. — LowlyDBA - John M
– LowlyDBA - John M, Commented Apr 17, 2018 at 12:40
Don't do this. It's a security nightmare. Look at this post for a smarter solution. — Erik Darling
– Erik Darling, Commented Apr 17, 2018 at 12:41

SQLDan · Accepted Answer · 2018-04-17 16:22:30Z

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

Stack Exchange Network

SQL SERVER dynamic Query with Like Statement for null values

1 Answer 1

Your Answer

Hot Network Questions

SQL SERVER dynamic Query with Like Statement for null values

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions