July 6, 2009 at 9:48 pm
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.
July 6, 2009 at 10:07 pm
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?
July 6, 2009 at 11:08 pm
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
July 6, 2009 at 11:16 pm
we still need to see the different scenarios that may be present inthe values provided in the variable.
July 7, 2009 at 1:08 am
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
July 8, 2009 at 10:38 am
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
July 8, 2009 at 1:32 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply