CODEWITHSUNDEEP
sqlsql-server
Aslam Jiffry
Aslam Jiffry

Reputation: 1316

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

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)

Error

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)

ERROR

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)

Error

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

Upvotes: 10

Views: 13431

Answers (9)

Irawan Soetomo
Irawan Soetomo

Reputation: 1325

Anything with the arithmetic of NULL will yield NULL.

I'd do this way if NULL is meant for returning all rows:

SET @query ='Select * FROM [A06].[Syn_RMDemand]' +
            case when @Id is not null then 
            ' WHERE RMHierarchyId = ''' + @Id + '''' else '' end

And this way if to really want to have rows with NULL condition:

SET @query ='Select * FROM [A06].[Syn_RMDemand]
             WHERE RMHierarchyId ' + case when @Id is null 
             then 'is NULL' else '= ''' + @Id +'''' end

But then again, it would always be better to avoid dynamic query at all cost. Consider:

Select * FROM [A06].[Syn_RMDemand] WHERE  
             (RMHierarchyId = @Id or nullif(@Id, '') is null)

And:

Select * FROM [A06].[Syn_RMDemand] WHERE (RMHierarchyId = @Id or
             (RMHierarchyId is null and @Id is null))

Upvotes: 3

Arunprasanth K V
Arunprasanth K V

Reputation: 21931

try this 

SET @query = 'Select * FROM [A06].[Syn_RMDemand] WHERE RMHierarchyId=''' + isnull(Convert(VARCHAR, @id), '') + ''' '

    EXEC (@query)

Upvotes: 1

tarzanbappa
tarzanbappa

Reputation: 4958

No need to convert it to NULL just check with your value

DECLARE @Id nvarchar(max);
DECLARE @query nvarchar(max);
 SET @Id = ''


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

Upvotes: 1

Eric
Eric

Reputation: 5733

I am curious why no one suggesting there is no need to use dynamic SQL here

Select * 
FROM [A06].[Syn_RMDemand]
WHERE NULLIF(@Id, '') IS NULL OR RMHierarchyId = @Id

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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...

Upvotes: 3

Deepshikha
Deepshikha

Reputation: 10264

You can also write as:

SET @query ='Select * FROM [A06].[Syn_RMDemand]
             WHERE 1 = case when '''+@Id+''' = '''' then 1 when  RMHierarchyId =      
             '''+@Id+''' then 1 else 0 end '

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

DECLARE @Id nvarchar(max);
DECLARE @query nvarchar(max);

SET @query  = N'SELECT * FROM [A06].[Syn_RMDemand] ' + CHAR(10)

IF @Id IS NOT NULL BEGIN
    SET @query = @query + N'WHERE RMHierarchyId = @param_id'
END
ELSE BEGIN
    SET @query = @query + N'WHERE RMHierarchyId IS NULL'
END

PRINT @query

EXEC sp_executesql
        @statement = @query,
        @params = N'@param_id VARCHAR(MAX)',
        @param_id = @Id

You should used parameterized query when using dynamic sql to avoid SQL Injection. Read this for more information.

If you want to return all rows if the @Id passed is NULL, just remove the ELSE part.

Upvotes: 0

ughai
ughai

Reputation: 9880

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

Upvotes: 4

Nitin Varpe
Nitin Varpe

Reputation: 10694

Try this.

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

Upvotes: 1

Related Questions