SQL Query passing null and value in where clause

Question

0.00/5 (No votes)

See more:

I have a stored Procedure with 2 input parameters. one is Type and other is Institute.

Institue may be null sometime. So if the institute is null it should not be in where condition.

For example I should have query like below. Can I do with single query? Not using IF.

SQL

IF @type =1  and @Inst = 'abc'

Select * from Test
where Type = @type and Institute = @Inst 

If @type =1  and @Inst = null

Select * from Test
where Type = @type

I need the above example with one select with out using IF coniditon. IS that possible?

Thanks for your help.

Posted 5-Mar-13 4:09am

vasini

Updated 20-May-18 23:49pm

Maciej Los

v2

Add a Solution

Comments

Richard C Bishop 5-Mar-13 10:14am

You can use case statements.

7 solutions

Solution 5

Hi,

Check the following links...

you will get Idea about NULL

Handling Null Values [^]
Handling NULL values in SQL Server 2005[^]
NULL, NULL, NULL and nothing but NULL[^]
NULL Functions[^]
A Puzzle – Fun with NULL[^]

Regards,
GVPrabu

Posted 5-Mar-13 18:00pm

gvprabu

Updated 5-Mar-13 18:09pm

v3

Solution 4

SQL

Select * from Test
where Type = @type and (@Inst is null or Institute=@Inst)

Posted 5-Mar-13 17:16pm

Davidduraisamy

Updated 20-Oct-15 21:38pm

v2

Comments

D.Nikolov 9-Mar-13 18:42pm

This is simpler than mine solution. I would use this when possible.

Solution 3

Try this.

SQL

DECLARE @UserName nvarchar(40), @Password nvarchar(10)

set @UserName='admin';
set @Password=null;

declare @sql nvarchar(4000);

set @sql='Select * from AdminUser where (';

if( @UserName is not NULL)
BEGIN
set @sql=@sql + ' UserName = '''+@UserName +'''';
END

if( @Password is not NULL)
BEGIN
if( @Password is not NULL)
set @sql=@sql + ' and Password = '''+@Password +'''';
END

set @sql=@sql + ')';

print @sql
exec sp_executesql @sql

Happy Coding :)

Posted 5-Mar-13 4:47am

dpalash

Solution 6

Select * from Test
WHERE
Type = ISNULL(@type,type)
AND Institute = ISNULL(@Inst,Institute)

Posted 19-Oct-15 20:34pm

Web_Spider

Updated 19-Oct-15 20:35pm

v2

Comments

jaket-cp 20-Oct-15 3:47am

This question is more than 2 years old, not a good idea to answer old questions.
Also this question has an accepted solution.
On top of that, it does not appear as though your solution will get what the op asked for.

Solution 7

decalre @ins varchar(50)

if @ins = null
begin
@ins = ''
end

Select * from Test
where Type = @type and Institute like '%' + @ins + '%'

I hope it will help you !

Posted 20-Oct-15 21:26pm

Member 12071825

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)

ZurdoDev · Accepted Answer · 2013-03-05T04:21:00

Solution 1

SQL

Select * from Test
where Type = @type and (Institute = @Inst OR @Intst IS NULL)

Posted 5-Mar-13 4:21am

ZurdoDev

Comments

vasini 5-Mar-13 10:31am

Thanks for the answer. But it includes records when inst is NULL. I need includes records when its passed else just condition with type.

ZurdoDev 5-Mar-13 11:03am

I don't quite understand what your saying, but I think it is this: (Institute = @Inst OR (@Intst IS NULL AND @type = 1))

D.Nikolov · Accepted Answer · 2013-03-05T04:37:00

SQL

select * from Test
where Type = @type and 
(coalesce(@inst, 1) = 1  or Institute = @inst)

coalesce returns first of its argument which is not null, so when @inst is null your condition will be like Type = @type and (1=1 or Institute = null).
The case when @inst has value will be Type = @type and ('abc' = 1 or Institute = 'abc').
I hope this helps.

SQL Query passing null and value in where clause

7 solutions

Solution 1

Solution 2

Solution 5

Solution 4

Solution 3

Solution 6

Solution 7

Add your solution here

Preview 0

Existing Members

...or Join us