How to handle null values in dynamic query in SQL server?

Question

0.00/5 (No votes)

See more:

Hi all,
I want to manage dynamically null values in SQL Server procedure. I have prepared following query, where assumed @storecode is the input parameter of stored procedure

SQL

declare @sql nvarchar(2000)
declare @storecode int =null

SET @sql = 'SELECT * FROM mch WHERE storecode = '+convert(varchar,@storecode)+' OR '+convert(varchar,@storecode)+' IS NULL'

print @sql 
EXECUTE sp_executesql @sql

When I pass valid integer value in @storecode it gives desire output, but when I tried to pass @storecode=NULL then it is not giving any output. I want to pass @storecode=NULL

What I have tried:

How to handle null values in dynamic query in SQL Server?

Posted 11-May-16 21:38pm

stellus

Updated 13-May-16 0:07am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Answer 1 · 2016-05-11T21:57:00

Solution 1

SQL

declare @sql nvarchar(2000)
declare @storecode int =null
set @storecode = null
SET @sql = N'SELECT * FROM mch WHERE ' + CASE WHEN cast ( @storecode as varchar) IS NULL THEN 'storecode IS NULL' ELSE 'storecode = ' +   cast( @storecode as varchar) END
 print @sql 
 exec( @sql)

updated solution, as per Chill's comment

Posted 11-May-16 21:57pm

Karthik_Mahalingam

Updated 11-May-16 22:22pm

v2

Comments

CHill60 12-May-16 4:03am

If the OP attempts to run the sql an error occurs "Must declare the scalar variable "@storecode"."

Karthik_Mahalingam 12-May-16 4:10am

HI Chill,
I am sorry, i was focused on building the query rather than testing it with exec,
I will come back with a solution..

CHill60 · Answer 2 · 2016-05-11T22:07:00

It would appear that you want to filter the results if @storecode is provided but if it is null then you want the entire table returned.

Probably the easiest/clearest way is this

declare @sql nvarchar(2000)
declare @storecode int = null
 
SET @sql = 'SELECT * FROM mch '  
IF @storecode IS NOT NULL 
	SET @sql = @sql + 'WHERE storecode = ' + CAST(@storecode as varchar)

The reason that you are not getting anything with your current query is because the use of NULL within the construction of the string renders the entire string NULL.

Hafiz Saad Ullah · Answer 3 · 2016-05-13T00:07:00

Solution 3

Its simple. USE ISNULL function to handle null value

SQL

declare @sql nvarchar(2000)
declare @storecode int =null
 
SET @sql = 'SELECT * FROM mch WHERE storecode = '+convert(varchar,ISNULL(@storecode,1))+''
 
print @sql 
EXECUTE sp_executesql @sql

Thanks.

Posted 13-May-16 0:07am

Hafiz Saad Ullah

Updated 13-May-16 0:09am

v2

Comments

CHill60 13-May-16 6:24am

I disagree. This will return the same as if @storecode = 1 - which does not appear to be what the OP wants. If there is a default value then, yes this would work. But that behaviour does not fit any real business scenario so is unlikely