using @@rowcount inside subquery

raghava_dg
raghava_dg used Ask the Experts™
on
Hi ,

I am faceing a prob with subquery .
Ex:

select * from table1 where col1 in (select col1 from table2)

Here my subquery "select col1 from table2" will return more than one row .So i need to use " IN " clause .

Suppose the result of sub query is zero i,e @@rowcount=0
then i should be able to give a default value to the field col1.

I don't want to use CURSOR since the result set is very large which may affect my performance of proc.

My question is can i use @@rowcount inside a subquery .
or is there any way to solve above situation.

Any suggetions will be appreciated.

Thanks in advance
raghav
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I am not sure whether I understood your problem, but I think you should consider the problem in a set oriented manner rather than row oriented. If you write the same query as

select distinct table1.* from table1, table2
where table1.col1 = table2.col1

or with outer join if you like to include all rows in table1:

select distinct table1.* from table1, table2
where table1.col1 *= table2.col1
you would have enough flexibility to play with result set. If you need to modify the value of col1 in result set depending on table2.col1 you can use "case" like:

select ...,
       case when table2.col1 is null then "XXX"
            else table1.col1
       end
from ....

Author

Commented:
Thanks alpmoon for the comment.

Yes i can use joins ..but my problem is as i said in the question ,suppose my subquery does not returns any row then i need to pass a default value to the outer query .

Actually here col1 itself is not NULL ,  entire subquery returns NULL ,in other words it does not return any row . At that time i need to give default value.How can it be done.

Commented:
I am not absolutely sure, but you should try "case":

select distinct table1.col2, table1.col3, ...,
      case when table2.col1 is null then "Your default value"
           else table1.col1
      end
from table1, table2
where table1.col1 *= table2.col1

I think table2.col1 would be treated as null when there is no corresponding row in table2. Or do you need to eliminate some of the rows in table1?

if exists (select 1 from table2)
begin
select * from table1 where col1 in (select col1 from table2)
end

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial