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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.