Stored proc - Parameter issue

I have an SSRS combo box that will pass 1 of 3 values to my stored proc.

'ALL'
'NATIVE'
'LINKED'


I need the report to do the following based on one of these 3 options being picked.

If the parameter Native is picked...then it should be be equal to the columns value in the report of Native.
the same if Linked is chosen..

If All is chosen it should return anything in this column...which could be "Native" , "Linked", '' , NULL

Please help me build the where clause to handle this.

Here is what I have ..but its not working.


(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or ((@NativeLinked = 'All') and t2.[Native/Linked] is null or  @NativeLinked = 'Native' or @NativeLinked= 'Linked'))
LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Could you try something like following:

select @NativeLinked = nullif(@NativeLinked, 'All');        -- if 'All' is passed make is null.

Open in new window


in where clause:

where 1 =  case when @NativeLinked is null then 1 else case when t2.[Native/Linked] = @NativeLinked then 1 else 0 end end

Open in new window

0
Robb HillSenior .Net DeveloperAuthor Commented:
i cannot use the nullif function....using comptibility mode 100
0
Robb HillSenior .Net DeveloperAuthor Commented:
let me rephrase for clarity..

I have a parameter that will pass one of three values  ( Linked, All, or Native"

All does not exists....All simply means both Linked and Native...but I also have to include blanks and nulls in the All as well so all records get returned on the All selection..  Another way to say is All means just ignore the parameter.

Chosing linked or native should return only rows where the column is equal to that.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Nitin SontakkeDeveloperCommented:
select @NativeLinked = case when @NativeLinked = 'All' then null else @NativeLinked end;
0
Robb HillSenior .Net DeveloperAuthor Commented:
This is a where clause...

And  When the parameter passed is 'All' is should be every value...including nulls  

This select you made reads to me when its all then make it null...else use the other value.

It should be when its all use null and all values...else use one or the other.
0
Pawan KumarDatabase ExpertCommented:
>>This is a where clause...
And  When the parameter passed is 'All' is should be every value...including nulls  

Hi Robb,
Below will give you exactly as you needed.


You have to use like below-

DECLARE @NativeLinked AS VARCHAR(100) 

SELECT * FROM yourtableName
WHERE ( [Native/Linked] =  @NativeLinked OR @NativeLinked = 'All' )

Open in new window


OR

DECLARE @NativeLinked AS VARCHAR(100) 

IF @NativeLinked = 'All' 
SET @NativeLinked = NULL 

SELECT * FROM yourtableName
WHERE ( [Native/Linked] =  @NativeLinked OR @NativeLinked IS NULL )

Open in new window

1
Nitin SontakkeDeveloperCommented:
I believe that it has been taken care of in suggested WHERE clause. If you believe not, please give scenario.
0
Robb HillSenior .Net DeveloperAuthor Commented:
hmmmmm..interesting...trying that
0
Robb HillSenior .Net DeveloperAuthor Commented:
Nitin..not following your use of numbers in your where clause
0
Pawan KumarDatabase ExpertCommented:
Hi Robb,

Please try my full tested solution. Just addition to my existing solution-

CREATE TABLE TestParm
(
	[Native/Linked] VARCHAR(50)
)
GO

INSERT INTO TestParm VALUES ( 'NATIVE' ) ,  ( 'NATIVE' ) , ('LINKED') , (NULL) , (NULL)
GO

DECLARE @NativeLinked AS VARCHAR(100)  = 'All'

IF @NativeLinked = 'All' 
	SET @NativeLinked = NULL 

SELECT * FROM TestParm
WHERE ( [Native/Linked] =  @NativeLinked OR @NativeLinked IS NULL )

Open in new window


OUTPUT FOR value ALL

DECLARE @NativeLinked AS VARCHAR(100)  = 'All'

IF @NativeLinked = 'All' 
	SET @NativeLinked = NULL 

SELECT * FROM TestParm
WHERE ( [Native/Linked] =  @NativeLinked OR @NativeLinked IS NULL )

Open in new window


/*------------------------
OUTPUT
------------------------*/
Native/Linked
--------------------------------------------------
NATIVE
NATIVE
LINKED
NULL
NULL

(5 row(s) affected)

Open in new window


DECLARE @NativeLinked AS VARCHAR(100)  = 'Native'

IF @NativeLinked = 'All' 
	SET @NativeLinked = NULL 

SELECT * FROM TestParm
WHERE ( [Native/Linked] =  @NativeLinked OR @NativeLinked IS NULL )

Open in new window



OUTPUT FOR Native

/*------------------------
OUTPUT
------------------------*/
Native/Linked
--------------------------------------------------
NATIVE
NATIVE

(2 row(s) affected)

Open in new window


OUTPUT FOR Value LINKED

DECLARE @NativeLinked AS VARCHAR(100)  = 'LINKED'

IF @NativeLinked = 'All' 
	SET @NativeLinked = NULL 

SELECT * FROM TestParm
WHERE ( [Native/Linked] =  @NativeLinked OR @NativeLinked IS NULL )

Open in new window


/*------------------------
OUTPUT
------------------------*/
Native/Linked
--------------------------------------------------
LINKED

(1 row(s) affected)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nitin SontakkeDeveloperCommented:
We are trying to arriving at the equality here and nothing more. Just to confuse people further, I even used to use different numbers everytime I write such case statements, for example:

where 99 = case .....then 99 else 98 end.

And typically developer keeps concentrating on 99 and 98 where they shouldn't. Hope you got it.
0
Robb HillSenior .Net DeveloperAuthor Commented:
Thanks for the detailed solution....testing the solution worked....thanks im on an all nighter deadline:)
0
Pawan KumarDatabase ExpertCommented:
Welcome. Glad to help as always.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.