sp parameter used in WHERE

Question

Post reply

sp parameter used in WHERE

Tim Fitzgerald

Valued Member

Points: 70
More actions
December 6, 2006 at 6:18 pm

#153325

I went through the first couple pages of threads and didn't see anything that would help me, so pardon me if I'm bringing up something that has been covered.
consider the following,
select somedataAge,somedataGender from somedatabase where somedataGender=0 and somedataAge=@desiredAge
The parameter can be null or blank so I need to evaluate that fact in the WHERE clause; so if '@desiredAge' is either null or blank, just return all records otherwise include it.
How can this be accomplished without having to have identical statements and just select the one to execute based on the procedure parameter?
Thanks,
Tim

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

use IS NULL or ISNULL( ) like:

where colname = ISNULL( @var, '' ) OR colname IS NULL

Sergiy SSC Guru Points: 110208 More actions · Answer 2

SET @DesiredAge = NULLIF(@DesiredAge , '')

SELECT ....

WHERE (somedata = @DesiredAge OR @DesiredAge IS NULL)

_____________
Code for TallyGenerator

Tim Fitzgerald Valued Member Points: 70 More actions · Answer 3

I think I left out some important criteria..

If the parameter is NULL or blank, I don't want to include it in the WHERE clause.

If it's not null, then I want to include it..

select column1,column2 from mydatabase where column1=0 [here, I need to include 'and column2=@param1' IF param1 is not null, otherwise ignore @param1]

Hopefully that makes better sense.

Thanks!

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

Is this what you're looking for ?!?! (run against Pubs database)...

create procedure testProc 
@au_id varchar(15) = NULL
as

if @au_id is NULL
select * from dbo.authors
else
select * from dbo.authors where au_id = @au_id
go

exec testProc
exec testProc '172-32-1176'

drop proc testProc

**ASCII stupid question, get a stupid ANSI !!!**

Sergiy SSC Guru Points: 110208 More actions · Answer 5

> [here, I need to include 'and column2=@param1' IF param1 is not null, otherwise ignore @param1]

That's exactly what my script is doing.

_____________
Code for TallyGenerator

Tim Fitzgerald Valued Member Points: 70 More actions · Answer 6

Sergiy,

I'll try that when I get back to the office tomorrow.

Thanks!

Tim Fitzgerald Valued Member Points: 70 More actions · Answer 7

Hi Sushila,

Sort off.. I really just want to tack on the 'au_id = @au_id' if @au_id is not null or blank.

I will try the suggestions everyone offered, tomorrow, when I get back to the office.

Thanks!

Tim Fitzgerald Valued Member Points: 70 More actions · Answer 8

Tim Fitzgerald

Valued Member

Points: 70

December 6, 2006 at 8:14 pm

#676503

Thanks john!