[Webinar] Streamline your web hosting managementRegister Today

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

SQL Loop with a Variable

Hi

I have a 7 queries that run based on a variable.... the variable can change several times...I want to add a loop to the query so it skips through each of the codes ...
ie:
Declare @Code as VARCHAR(3)
Declare @Month as VARCHAR(10)

Set @Month =  'Month 1' ............this stays in stone till the following month
Set @Code = 'AAA'

Now there is a table called tbl_test
that has a list of 7 codes, AAA, BBB, CCC, etc...

There are several Insert queries that are reliant on the @Code

So I want the SQL to loop through the tbl_test .....
0
flickimp1717
Asked:
flickimp1717
  • 3
  • 2
1 Solution
 
indianguru2Commented:
See the script below.
-- Dump the data from the Table into a Temp Table

SELECT  IDENTITY( NUMERIC(18), 1, 1 ) AS Seq ,
        Code
INTO    #tmpT
FROM    tbl_test 

DECLARE @RowNum INT ,
    @RowCount INT
SET @RowNum = 1
SET @RowCount = ( SELECT COUNT (1) FROM #tmpT
                )
                       
IF @RowCount > 0 
    BEGIN
        WHILE ( @RowNum <= ( SELECT MAX (Seq) FROM #tmpT
                           ) ) 
            BEGIN
                DECLARE @Code AS VARCHAR(3)
                DECLARE @Month AS VARCHAR(10)

                SET @Month = 'Month 1'
                --- Get the Code from the table here.
                SELECT  @Code = Code
                FROM    #tmpT
                WHERE   Seq = @RowNum
                
                
                --- Do Your thing here.
                
                
                SET @RowNum = @RowNum + 1
            END
    END

Open in new window

0
 
flickimp1717Author Commented:
Thanks for that
Wil have a go and let you know how it goes
0
 
flickimp1717Author Commented:
I'm a little confused over how the loop bit works and if I really need to sue a temp table?

What I require is....

@Code = AAA
then run all queries

then @Code = BBB
then run all queries

etc....
0
 
flickimp1717Author Commented:
Sorry, the code here is excellent and does the job

Many thanks
0
 
indianguru2Commented:
The temp table is used for the Unique Identity Column which it auto generates and impersonates as a row number which is the easiest solution.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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