×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to disregard passed parameter to stored pro?

How to disregard passed parameter to stored pro?

How to disregard passed parameter to stored pro?

(OP)
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

RE: How to disregard passed parameter to stored pro?

CODE

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

RE: How to disregard passed parameter to stored pro?

(OP)
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.

RE: How to disregard passed parameter to stored pro?

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

RE: How to disregard passed parameter to stored pro?

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

RE: How to disregard passed parameter to stored pro?

(OP)
Thanks Markros.

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

thanks

RE: How to disregard passed parameter to stored pro?

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

RE: How to disregard passed parameter to stored pro?

(OP)
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.

RE: How to disregard passed parameter to stored pro?

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

CODE

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

RE: How to disregard passed parameter to stored pro?

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

RE: How to disregard passed parameter to stored pro?

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

PluralSight Learning Library

RE: How to disregard passed parameter to stored pro?

Markros,

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

CODE

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

RE: How to disregard passed parameter to stored pro?

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

I guess you need to resort to dynamic SQL instead.

PluralSight Learning Library

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close