loop through query in stored procedure

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)

loop through query in stored procedure

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-21 : 17:18:13

I have a table called customers --
now I want to loop through customers and show the main fields but then i also want to show 3 other fields that are queries themselves based on the customer id -- like select count(orderid) from orders where customerid=@customerid and active=1

and then 2 other queries -- how can I make a stored procedure that can loop through the table -- so my other quries and return a new full recordset?
what's the best way to do this?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-21 : 22:00:52

you don't need to do any looping for this.


select  c.customerid
        ,c.<otherCustomerColumns>
        ,count(orderid) as OrderCount
from    customers c
INNER JOIN orders o 
          on o.customerid = c.customerid
where    c.active = 1
group by c.customerid
         ,c.<otherCustomerColumns>

Be One with the Optimizer
TG

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-22 : 02:57:14

the problem is besides for the count I need in the same query
max(date) where paid=1 (from orders table)
min(date) where paid=0 (from orders table)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-22 : 04:33:54

try like this
select <columns>, case when paid = 1 then max(date) else min(date) end from orders
group by <columns>

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-22 : 04:44:51

but I need both columns in my query

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-22 : 07:47:05

quote:
Originally posted by bklr

try like this
select <columns>, case when paid = 1 then max(date) end maxval,~~else~~
case when paid = 0 then min(date) end minval from orders
group by <columns>

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-22 : 08:13:39

incorrect syntax near keyword case
what am i doing wrong?

select c.customerid,firstname,lastname,sum(s.amount) as balance from customers c ,
case when s.paid=1 then max(date) as last,
case when s.paid=0 then min(date) as next
inner join orders s on
s.customerid=c.customerid and s.paid=1,

where c.active=1
group by c.customerid,firstname,lastname

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-22 : 08:39:20

last = max(case when s.paid = 1 then date else null end),
next = min(case when s.paid = 0 then date else null end)

Be One with the Optimizer
TG

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-22 : 09:27:52

quote:
Originally posted by esthera

incorrect syntax near keyword case
what am i doing wrong?

select c.customerid,firstname,lastname,sum(s.amount) as balance,case when s.paid=1 then max(date) as last,case when s.paid=0 then min(date) as next from customers c ,
case when s.paid=1 then max(date) as last,
case when s.paid=0 then min(date) as next
inner join orders s on
s.customerid=c.customerid and s.paid=1,

where c.active=1
group by c.customerid,firstname,lastname

case statement should be written before from clause

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-22 : 14:21:55

I did that and it still errored

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-22 : 14:27:01

the error now is Incorrect syntax near the keyword 'as'.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-22 : 22:59:02

you left out the END of the CASE statement - see my example above. If you still are getting an error post the statement as it is now and what the error is.

Be One with the Optimizer
TG

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-23 : 01:14:07

use END statement followed by the case statement ,and use it again i think u left out END keyword....

or post your full query which gives ur QUERY?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-23 : 09:27:35

ok thanks - I got it working except for 1 thinkg
I have sum(amount) but I want it to be only the sum of amount of paid orders - if i add the paid clause at the end then the next will never show up --
how can i do this ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-23 : 09:38:29

with a CASE statement similar to my previous example:
sum(case when s.paid=1 then amount else 0 end) as [totalAmountPait]

Be One with the Optimizer
TG

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-23 : 13:35:07

thanks so much for all your help - i'll have to practice with all these queries -- it seems very useful

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-23 : 13:56:40

You're welcome and remember, database efficiencies are built into "set-based" operations so whenever you find yourself wanting to iterate through a set of rows one at a time, stop yourself and think how you can I do it all in one shot :)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources