Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 37775
  • Last Modified:

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?
0
seahawk9
Asked:
seahawk9
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now