For Loop in T-SQL?

I am trying to write a new stored proc based off an exisiting one that counted the number of ads placed and inserted the data into a table. But now I want to break it down by class, so I am wondering if there is such a thing as a For Loop in T-SQL. (I am usually an ASP programmer and only put on the db hat when needed).
So here is the existing Stored Proc:
      declare
            @PAID int,
            @FREE int

            select @FREE = count(*)
            from submitqueue
            where cost = 0
                  and processstatus not in (4,5,6)
      
            select @PAID = count(*)
            from submitqueue
            where cost > 0
                  and processstatus not in (4,5,6)
      
            BEGIN
                  /* INSERT FREE AD STATS */
                  insert into ADCOUNTS (
                        DATE,
                        COUNT,
                        TYPE
                  )
                  values (
                        getdate(),
                        @FREE,
                        'FREE'
                  )
                  /* INSERT PAID AD STATS */
                  insert into ADCOUNTS (
                        DATE,
                        COUNT,
                        TYPE
                  )
                  values (
                        getdate(),
                        @PAID,
                        'PAID'
                  )            
                                END
 

I have created the new table called ADCOUNTSBYCLASS and it is the same makeup of ADCOUNTS except it had a new field called CLASS. Our classes go from 10 to 350 and have an increment of 10. So I need to do something like:

      declare
            @PAID int,
            @FREE int
                                @CLASS int

                                For @CLASS = 10 to 350 step 10
            select @FREE = count(*)
            from submitqueue
                      where cost = 0
            and processstatus not in (4,5,6)
                                and class = @CLASS
      
            select @PAID = count(*)
            from submitqueue
            where cost > 0
            and processstatus not in (4,5,6)
                                and class = @CLASS

      
            BEGIN
                  /* INSERT FREE AD STATS */
                  insert into ADCOUNTSBYCLASS (
                        DATE,
                        CLASS,
                        COUNT,
                        TYPE
                  )
                  values (
                        getdate(),
                        @CLASS,
                        @FREE,
                        'FREE'
                  )
                  /* INSERT PAID AD STATS */
                  insert into ADCOUNTSBYCLASS (
                        DATE,
                        CLASS,
                        COUNT,
                        TYPE
                  )
                  values (
                        getdate(),
                        @CLASS
                        @PAID,
                        'PAID'
                                            NEXT
    END

Can you do something like this in T-SQL? Or do I have to hardcode each instance of class?
seahawk9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RDWaibelCommented:
Here you go:

 declare
          @PAID int,
          @FREE int,
          @CLASS int
         
While @Class <= 350 BEGIN
--          For @CLASS = 10 to 350 step 10
  select @FREE = count(*)
    from submitqueue
      where cost = 0
        and processstatus not in (4,5,6)
        and class = @CLASS
     
  select @PAID = count(*)
    from submitqueue
      where cost > 0
        and processstatus not in (4,5,6)
        and class = @CLASS

    /* INSERT FREE AD STATS */
    insert into ADCOUNTSBYCLASS (
      DATE,
      CLASS,
      COUNT,
      TYPE
      )
      values (
        getdate(),
        @CLASS,
        @FREE,
        'FREE'
        )
    /* INSERT PAID AD STATS */
    insert into ADCOUNTSBYCLASS (
      DATE,
      CLASS,
      COUNT,
      TYPE
      )
      values (
        getdate(),
        @CLASS
        @PAID,
        'PAID'
        )
  Select @Class = @Class + 10

END

There is not a FOR...Next loop so you can use the While...Begin...End (as seen above!)

Happy DBA-ing!  

=)  Rob
0
lengreenCommented:
Hi

It would be something like this (your code inside the loop may need adjusting)

 declare @PAID int
 declare @FREE int
 declare @CLASS int

Set @CLASS = 10
While @CLASS < 350
      Begin
          select @FREE = count(*)
          from submitqueue
                     where cost = 0
          and processstatus not in (4,5,6)
                                and class = @CLASS

     
          select @PAID = count(*)
          from submitqueue
          where cost > 0
          and processstatus not in (4,5,6)
                                and class = @CLASS

     
         
               /* INSERT FREE AD STATS */
               insert into ADCOUNTSBYCLASS (
                    DATE,
                    CLASS,
                    COUNT,
                    TYPE
               )
               values (
                    getdate(),
                    @CLASS,
                    @FREE,
                    'FREE'
               )
               /* INSERT PAID AD STATS */
               insert into ADCOUNTSBYCLASS (
                    DATE,
                    CLASS,
                    COUNT,
                    TYPE
               )
               values (
                    getdate(),
                    @CLASS,
                    @PAID,
                    'PAID')

             Set @CLASS = @CLASS+10

      End
0
RDWaibelCommented:
You will need to add a line BEFORE the while statement that says:

Select @Class = 10
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Lori99Commented:
If you don't need a row for every class, only those that have ads, you could do this with a couple of single SQL statements and no loop like this.

INSERT INTO ADCOUNTSBYCLASS
SELECT
 getdate(),
 CLASS,
 COUNT(*),
 MAX(TYPE)
FROM  submitqueue
where cost = 0
and processstatus not in (4,5,6)
group by CLASS

insert into ADCOUNTSBYCLASS
select getdate(),
           CLASS,
           COUNT(*),
           'PAID'
from submitqueue
where cost > 0
and processstatus not in (4,5,6)
group by CLASS
0
LowfatspreadCommented:
don't understand why you're making it so complex....

why not just do it with the 1 insert statement?


    /* INSERT AD STATS */
    insert into ADCOUNTSBYCLASS (
      DATE,
      CLASS,
      COUNT,
      TYPE
      )
     Select getdate(),Class,Count(*),Type
       From (    
     select class
              ,case when cost > 0 then 'PAID' else 'FREE' end as Type
        From SubmitQueue
       Where processStatus not between 4 and 6
             ) as X
       group by Class,Type


if you really need to restrict the classes

then add

 and Class between 10 and 350
 and 0 = class % 10
 
to the where clause

 
   
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BillAn1Commented:
there is no FOR NEXT, but there is a WHILE loop :

set @x = 1
while @x < 10
begin
     ......
     set @x = @x + 1
end

HOWEVER, you are much better off not doing a loop if you do not need to do so. Instead, you should always try to do set-based SQL statements where possible. In this case, you will be best off creating either a table, or a function which contains / generates the 35 CLASS values you want, and then a sinlge insert statement will do all the counts in one go :

try something like this :


create function dbo.tblClassType()
returns @tab table (class integer, type char(4))
as
begin
      declare @class integer
      set @class = 10
      while @class <= 350
      begin
            insert into @tab values (@class, 'FREE')
            insert into @tab values (@class, 'PAID')
            set @class = @class + 10
      end
      return
end

--- this function will provde the list of CLASS and TYPEs you are interested in, then a single join with your data, you can do it all in one insert.


        insert into ADCOUNTSBYCLASS (DATE,CLASS, COUNT, TYPE )
        select getdate(), cl.class, count(res.class),
            cl.type
        from  dbo.tblClassType() cl left outer join (
            select class,
            case when processstatus in (4,5,6) then 'PAID' else 'FREE' end as type
            from submitqueue
            where cost = 0
        ) res
                 on cl.class = res.class and cl.type = res.type
                group by  cl.class, cl.type
0
fds_fatboyCommented:
How about forgetting a loop and doing it using SQL?

INSERT INTO ADCOUNTSBYCLASS (DATE,CLASS,COUNT,TYPE), SELECT getDate(), COUNT(*) ,class, 'FREE' FROM  submitqueue WHERE cost = 0 GROUP BY CLASS
INSERT INTO ADCOUNTSBYCLASS (DATE,CLASS,COUNT,TYPE), SELECT getDate(), COUNT(*) ,class, 'PAID' FROM  submitqueue WHERE cost != 0 GROUP BY CLASS

or something similar
0
BillAn1Commented:
sorry, I misinterpreted your original, the type is set by the cost, not the process status, so the SQL should be

insert into ADCOUNTSBYCLASS (DATE,CLASS, COUNT, TYPE )
       select getdate(), cl.class, count(res.class),
          cl.type
       from  dbo.tblClassType() cl left outer join (
          select class,
          case when cost > -  then 'PAID' else 'FREE' end as type
          from submitqueue
          where processstatus <= 4 or processstatus >= 6
       ) res
                 on cl.class = res.class and cl.type = res.type
                group by  cl.class, cl.type


And, of course, if you do not need the 0 counts, you regress back to the simplest query, the one suggested by LowFatSpread
0
BillAn1Commented:
that's a 0 not a - , sorry for all the posts.

insert into ADCOUNTSBYCLASS (DATE,CLASS, COUNT, TYPE )
       select getdate(), cl.class, count(res.class),
          cl.type
       from  dbo.tblClassType() cl left outer join (
          select class,
          case when cost > 0  then 'PAID' else 'FREE' end as type
          from submitqueue
          where processstatus <= 4 or processstatus >= 6
       ) res
                 on cl.class = res.class and cl.type = res.type
                group by  cl.class, cl.type
0
seahawk9Author Commented:
Thanks for all the feedback, Lowfatspread's answer worked just fine, although it looks like any number of choices would work. Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.