Passing Null value parameter to dynamic SQL query. How can this be successfully executed?

Question

I am executing a dynamic SQL query. where parameters are passed dynamically. I first wrote similar to following code.

DECLARE @Id nvarchar(max);
DECLARE @query nvarchar(max);
SET @Id ='RMH_108'
SET @query ='Select * 
             FROM [A06].[Syn_RMDemand]
             WHERE RMHierarchyId =' + @Id
PRINT @query
EXEC(@query)

Then I wrote this.

DECLARE @Id nvarchar(max);
DECLARE @query nvarchar(max);
SET @Id ='RMH_108'
SET @query ='Select * 
             FROM [A06].[Syn_RMDemand]
             WHERE RMHierarchyId = '''+@Id+''''
PRINT @query
EXEC(@query)

This time code successfully executed. Since parameter can be empty I need to convert that to null. I modified the code and wrote this

DECLARE @Id nvarchar(max);
DECLARE @query nvarchar(max);
SET @Id = ''
SET @Id = NULLIF(@Id,'')
-- COMMENTED SET @Id ='RMH_108'

SET @query ='Select * FROM [A06].[Syn_RMDemand]
             WHERE RMHierarchyId = '''+@Id+''''
PRINT @query
EXEC(@query)

No errors. Query crashed.
Does anybody come up with this sort of problem ?

Answer 1

You should use sp_executeSQL and remove string concatenation like this. I am assuming that if NULL is passed you want all the rows to be returned.

Query

DECLARE @Id nvarchar(max);
DECLARE @query nvarchar(max);
SET @Id ='RMH_108'SET @query ='Select * 
                               FROM [A06].[Syn_RMDemand]
                               WHERE RMHierarchyId = @Id OR @ID IS NULL'
PRINT @query
EXEC sp_executeSQL @query,N'@Id NVARCHAR(MAX)',@Id

Answer 2

Try the following:

SET @query ='Select * FROM [A06].[Syn_RMDemand]'
IF @Id IS NOT NULL
   SET @query = @query + ' WHERE RMHierarchyId = '''+@Id+''''

If the parameter comes from client you better do with parametrized query(see @ughai's answer) in order to exclude sql injection possibility...