How to disregard passed parameter to stored pro?

[Coyote2]

Hi,
I have stored procedure the is expecting few parameters.

Proj_id Int
Priority Int
type varchar

here is my scenario. Users HAVE to select a project; however, may or may not select a priority.
priority value is a dropdown list (1 or 2 or 3). my problem is if they don't select any value which will pass Null or 0 to the stored procedure I'm not sure how to ignore evaluating the parameter since it's part of my WHERE claus..
here is part of my code:
--------------------------------------------
Begin
SET NOCOUNT ON;

DECLARE @PROJ_ID numeric(10)
DECLARE @PRTY_ID AS NUMERIC (1)
DECLARE @PRTY_ALL AS VARCHAR (8)

SET @PROJ_ID = @PROJ
SET @PRTY_ID = @PRTY

SELECT .......
FROM .........
WHERE
PROJECT_ID = @PROJ_ID
AND PRIORITY = @PRTY_ID ---> if the user deoesn't select a priority value I want to show ALL priorities.

the same condition applies to my Type parameter.

---------------------------------------------
Any suggestion is highly appreciated.

thanks

 

[gmmastros]

Where PROJECT_ID = @PROJ_ID
      And (Coalesce(@Priority, 0) = 0 Or Priority = @Priority)

Your other option is to use dynamic SQL.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Coyote2]

George,
thank you but the only thing that I forgot to mention is that this query is really a subquery and when I use the syntax you provided it errors out.

it works when I place it at the very end of the query but not in the middle.

thank you.

 

[gmmastros]

It should work no matter where you put it. Can you post the error message and/or the entire query. Clearly something isn't right, but without more information it is nearly impossible to say what it is.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

You can use this instead

and Priority between coalesce(@Priority, 0) and coalesce(@Priority, 9999999999999) -- use the max priority that is correct for the Priority field type

PluralSight Learning Library

 

[Coyote2]

Thanks Markros.

George
what about the character parameter? the TYPE parameter is either a string or null. does your solution work as well?

thanks

 

[markros]

For character it's a bit more complex. If the character only allows alpha-numeric and you only use English letters, then
you can use between '' and replicate('z',len(Column)) assuming it's not a varchar(max) column.

Otherwise there is no solution similar to the integer value solution and you need to use either

IF
...

ELSE

or approach suggested by George but it may result in bad performance, or you may try constructing your SQL as dynamic
SQL.

Denis Gobo has a blog in regards to this - check
Do you use ISNULL(...). Don't, it does not perform




PluralSight Learning Library

 

[Coyote2]

Markros,
the possible values for the parameter passed are: null,0,1,2,3

your method doesn't work when 0 or null are passed but George's does.

This report get a parameter from anothe program and if the user doesn't select anything I'm not sure what gets passed to the report but it has to be either 0 or null. my goal is to show ALL records when null or 0 are passed.

For the other parameter, the value alpha-numeric US letters only. I'll try to mess with your method and let you know.

thanks for your help.

 

[gmmastros]

My suggestion should work equally well for strings. Specifically, if you want to ignore the filter criteria when the passed in string is null....

Where (@StringParameter Is NULL Or StringColumn = @StringParameter)
      And (@IntParameter Is NULL OR IntColumn = @IntParameter)

Be very careful about the correct placement of the parenthesis. If you're not careful about it, you'll get the wrong results.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Try:

select * from Table where intParameter between
coalesce(NULLIF(@Param,0),0) and COALESCE(NULLIF(@Param,0),99)

So, if the @Param is NULL or 0 you will get all records, but if the @Param is 1 or 2 or 3 you only get records corresponding to this value.

PluralSight Learning Library

 

[markros]

Sorry, in the above I meant intColumn between ...., of course.

PluralSight Learning Library

 

[gmmastros]

Markros,

Your proposed solution does not work if there are NULL's in the table for the intcolumn. Ex:

Declare @Temp Table(IntColumn Int)

Insert Into @Temp Values(NULL)
Insert Into @Temp Values(1)
Insert Into @Temp Values(2)
Insert Into @Temp Values(3)


Declare @IntParameter Int
Set @IntParameter = NULL


select * from @Temp 
where IntColumn between
coalesce(NULLIF(@IntParameter,0),0) and COALESCE(NULLIF(@IntParameter,0),99)

Specifically, if there is a row in the table where IntColumn is NULL and you pass NULL for the IntParameter, you will not get the row.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

You're right, this solution doesn't consider NULLs.

I guess you need to resort to dynamic SQL instead.

PluralSight Learning Library