Conditional Where clause which has 'in' .

Question

Conditional Where clause which has 'in' .

descentflower

Ten Centuries

Points: 1227
More actions
July 6, 2009 at 9:48 pm

#134039

Hi,
i have to select rows from a table based on an 'in' . I will pass a parameter to the procedure which can be ALL, 0 , 1
ex.
[Code]
select * from tbNames where nId in (select nId from tbOldNames where nParent=@nParent)
[/Code]
now i want the where clause to be included only when @nParent= 0 ,1 or anyother integer.
Can this be done with case ?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You'll have to excuse me, but I'm much more of a visual person, could you show us the various forms the query could take based on the various parameters passed?

PNS Right there with Babe Points: 773 More actions · Answer 2

See if this is what you want.

select * from tbNames where nId in (select nId from tbOldNames where nParent=@nParent OR @nParent='ALL')

The above query will return all the records when you pass ALL and specific record when you pass anything other then ALL. This is as good as saying there is no where clause when you pass ALL.

You might want to pass NULL or 0 instead of ALL becuase if your nParent is int then you have to convert it to varchar while comparing the where clause.Anyway that should be your call 🙂

Thanks

Parul

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

we still need to see the different scenarios that may be present inthe values provided in the variable.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

Sounds like you need dynamic SQL See this link or alternatively see this link [/url] for a "catch-all" query.

Any further questions , post back again

Clear Sky SQL
My Blog[/url]

david.tyler SSCrazy Points: 2206 More actions · Answer 5

To eliminate dynamic sql you could use an if statement like the following (used a simple date select to get the point across):

DECLARE @nParent NVARCHAR(3)

SET @nParent = 'ALL'

IF ISNUMERIC(@nParent) = 1

BEGIN

SELECT DATEADD(DAY,CAST(@nParent AS INTEGER),GETDATE())

END

ELSE

BEGIN

SELECT GETDATE()

END

terrance.steadman SSCrazy Points: 2097 More actions · Answer 6

descentflower (7/6/2009)
Hi,
i have to select rows from a table based on an 'in' . I will pass a parameter to the procedure which can be ALL, 0 , 1
ex.
[Code]
select * from tbNames where nId in (select nId from tbOldNames where nParent=@nParent)
[/Code]
now i want the where clause to be included only when @nParent= 0 ,1 or anyother integer.
Can this be done with case ?
Thanks.

Greetings,

Here is a possible way for you.

SELECT

*

FROM tbNames

WHERE

(

nid in (SELECT nid FROM tbOldNames WHERE nParent = @nParent)

AND ISNUMERIC(@nParent) = 1

)

OR @nParent = 'ALL'

)

I surrounded this in parentheses so you could later add other parameters that also have their own as = or ALL check. The ISNUMERIC function returns 1 if the value in @nParent is a valid number.

Have a good day.

Terry Steadman