combine result of 2 scripts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

combine result of 2 scripts

Author

Topic

van73
Starting Member

7 Posts

Posted - 2008-01-02 : 02:06:14

I have a need to write a store procedure to get a count of some column in the database based on conditions and send it out to the report

I wrote a procedure with input parameter @ClId varchar(20),
@ProdId varchar(20)

DECLARE @cnt INT;
DECLARE @Tot INT;
set @cnt = (select count(col1) from table1 innerjoin table2 on ....
innerjoin table3 on ...
where table2.clid = @Clid and table3.ProdId = @ProdId and <one condition>

This may or may not return rows that is 0 rows will be affected

This I have to combine with another script like

set @cnt = (select count(col1) from table1 innerjoin table2 on ....
innerjoin table3 on ...
where table2.clid = @Clid and table3.ProdId = @ProdId and <some other condition>

Now I used variable @Tot to combine both the counts like this
After the first script I tried

if @@rowcount = 0 set @cnt = 0;
select @Tot = @Tot + @cnt; (I have initialised @Tot = 0 at the beginning of Stored Proc.)

Again after the second script I have repeated the above lines

If both the scripts have rows returned I have no problem. But if the first script returns no rows, @cnt is not set to 0 - I think hence @Tot is not returning anything...

Am I doing something wrong. Is the usage if @@rowcount not appropriate here???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 03:22:40

It looks like you can achieve this much more simpler as:-

select count(col1) from table1 innerjoin table2 on ....
innerjoin table3 on ...
where table2.clid = @Clid and table3.ProdId = @ProdId and (<one condition> or <some other condition>)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-02 : 03:25:20

Select @tot=sum(counting)from
(
select count(col1) as counting from table1 innerjoin table2 on ....
innerjoin table3 on ...
where table2.clid = @Clid and table3.ProdId = @ProdId and <some other condition>
union all
select count(col1) from table1 innerjoin table2 on ....
innerjoin table3 on ...
where table2.clid = @Clid and table3.ProdId = @ProdId and <some other condition>
) as t

Madhivanan

Failing to plan is Planning to fail

van73
Starting Member

7 Posts

Posted - 2008-01-03 : 10:21:42

Thanks for the prompt reply Visakh and Madinanan
Your reply worked - M It was my mistake - V my select query is not the same in both scripts - I posted it wrongly.
in my second script - it is select count( distinct something)

I should have posted here before - I broke my head several times. Just curious why is my logic not wroking though its beating round the bush!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-04 : 07:33:17

quote:
Originally posted by van73

Thanks for the prompt reply Visakh and Madinanan
Your reply worked - M It was my mistake - V my select query is not the same in both scripts - I posted it wrongly.
in my second script - it is select count( distinct something)

I should have posted here before - I broke my head several times. Just curious why is my logic not wroking though its beating round the bush!!!

So, which one did you use?

Madhivanan

Failing to plan is Planning to fail

van73
Starting Member

7 Posts

Posted - 2008-01-06 : 20:17:13

This worked
Select @tot=sum(counting)from
(
select count(col1) as counting from table1 innerjoin table2 on ....
innerjoin table3 on ...
where table2.clid = @Clid and table3.ProdId = @ProdId and <some other condition>
union all
select count(col1) as counting from table1 innerjoin table2 on ....
innerjoin table3 on ...
where table2.clid = @Clid and table3.ProdId = @ProdId and <some other condition>
) as t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources