August 14, 2008 at 1:31 am
Hello,
Briefly, I have 3 params(@col1, @col2 & @col3) in a sp. and i have a table say, table1
columns [col1, col2, col3, othercols....]
I need to select all the rows, which mathces the params
for eg. if @col1 is <> 0 then i have a query like
select * from table1 where col1 = @col1 and @col1 <> 0
however, if @col1 <>0 and @col2 <>0 both are non-zero
then i have to select like,
select * from table1 where col1 = @col1 and col2 = @col2 and @col1 <>0 and @col2 <> 0
Though there lot 8 possible non-zero combination for this,
for the moment, I am writing all the possible combination for params for non-zero value and writing select statements accordingly.
Though, is there any way by using union or intersect or self-join to write this query easily? Let me know.
I appreciate your comments / suggestions
Thanks
KJ
August 14, 2008 at 8:30 am
The most common way I have seen recommended for this is to use dynamic SQL. Any other options depend on the behavior you want and the data you have. Are you AND'ing all the criteria together? Do all the columns being used as criteria default to 0? Solutions offered will vary based on these criteria as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2008 at 2:26 pm
Working from your example, something like this might work:
declare @col1 int, @col2 int, @col3 int
declare @tbl table (col1 int, col2 int, col3 int)
insert into @tbl values (1, 0, 0)
insert into @tbl values (1, 2, 0)
insert into @tbl values (3, 2, 1)
insert into @tbl values (0, 2, 1)
insert into @tbl values (3, 0, 1)
select @col1 = 3, @col2 = 0, @col3 = 1
select*
from@tbl
wherecol1 = isnull(nullif(@col1, 0), col1)
andcol2 = isnull(nullif(@col2, 0), col2)
andcol3 = isnull(nullif(@col3, 0), col3)
- Nate
_____________________________________________________________________
- Nate
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply