How to check parameter is not null value sql server

Question

How to check parameter has null or not in stored procedure

e.g

select * from tb_name where name=@name

i need to check if @name has values or null means.how to do it.thanks...

You can do something like select * from tb_name where @name IS NULL OR name=@name — Comfortably Numb
– Comfortably Numb, Commented Dec 26, 2013 at 16:57

Gordon Linoff · Accepted Answer · 2013-12-26 16:57:12Z

2

Is this what you want?

select * from tb_name where name=@name and @name is not null

Actually, the extra check is unnecessary, because NULL will fail any comparison. Sometimes, NULL is used to mean "get all of them". In that case, you want:

select * from tb_name where name=@name or @name is null

answered Dec 26, 2013 at 16:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Avi Turner · Accepted Answer · 2013-12-26 16:57:27Z

1

In case you want results where Name is not null and equal to @name Try:

select * from tb_name where name=@name AND @name IS NOT NULL

If you want results where Name is null Or equal to @name Try:

select * from tb_name where name=@name OR @name IS NULL

Where you looking for one of those?

answered Dec 26, 2013 at 16:57

Avi Turner

10.5k8 gold badges51 silver badges79 bronze badges

Comments

marc_s · Accepted Answer · 2013-12-26 17:01:52Z

1

select * 
from tb_name 
where ((@name  is null) or ([name] = @name))

edited Dec 26, 2013 at 17:01

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 26, 2013 at 16:57

Ryan Schlueter

2,2212 gold badges15 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to check parameter is not null value sql server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related