While loop in Microsoft SQL Server 2008

Question

I'm trying to come up with a simple example of a while loop using Microsoft SQL Server. Here I'm looping through all the tables that begin with the word temp.

Declare @Object_ID int,@Name varchar(50)
set @Object_ID = 0
while exists (
    select * from 
    sys.tables
    where type = 'u' 
    and object_ID > @Object_ID 
    and Name like 'Temp%' 
) BEGIN
    select top 1 @Object_ID=Object_ID,@Name=Name
    from sys.tables
    where type = 'u' 
    and object_ID > @Object_ID 
    and Name like 'Temp%' 
    order by Object_ID
    exec('Select ''' + @Name + ''' as TableName,count(*) AS Counter from ' + @Name)
END

My problem is: now that I've looped through the tables, how do I use the information I've gathered with my exec command? In other words, can I stored the table that is returned from the exec command into a variable?

Philip Kelley · Accepted Answer · 2009-12-11 15:09:19Z

2

Here is how I do this kind of task these days:

DECLARE
  @LoopId     int
 ,@Command    varchar(500)

DECLARE @List TABLE
 (
   TableName  sysname  not null
  ,LoopId     int      not null  identity(1,1)
 )

DECLARE @Results TABLE
 (
   TableName  sysname  not null
  ,Counter    int      not null
 )

--  Load with items you wish to review
INSERT @List (TableName)
 select name
  from sys.tables
  where Name like 'Temp%'
  order by Name desc

SET @LoopId = @@rowcount

--  Go through list and process each item
WHILE @LoopId > 0
 BEGIN
    SELECT @Command = 'SELECT ''' + TableName + ''' as TableName, count(*) as Counter from ' + TableName
     from @List
     where LoopId = @LoopId

    --  Load results in temp table
    INSERT @Results (TableName, Counter)
     execute (@Command)

    SET @LoopId = @LoopId - 1
 END

SELECT * from @Results

answered Dec 11, 2009 at 15:09

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phillip Senn Over a year ago

This was a great answer Philip, thank you! All my SQL are belong to you.

Jarrett Meyer · Accepted Answer · 2009-12-11 14:56:30Z

2

You can store a temp table, similar to this example.

answered Dec 11, 2009 at 14:56

Jarrett Meyer

19.6k6 gold badges61 silver badges52 bronze badges

Comments

Jammin · Accepted Answer · 2009-12-11 14:57:12Z

2

If i understand your question correctly then sure, look here

Just insert into your table var for each iteration then do whatever on it afterwards.

answered Dec 11, 2009 at 14:57

Jammin

3,1002 gold badges25 silver badges34 bronze badges

Comments

Tom H · Accepted Answer · 2009-12-11 15:04:15Z

2

If you want to gather information using dynamic SQL like that then you'll have to have a place to keep that information - a temp table, permanent table, or table variable. Then you should be able to do something like this:

Declare @Object_ID int,@Name varchar(50)
DECLARE
    @tbl    TABLE (table_name SYSNAME, table_count INT)

set @Object_ID = 0
while exists (
    select * from 
    sys.tables
    where type = 'u' 
    and object_ID > @Object_ID 
    and Name like 'Temp%' 
) BEGIN
    select top 1 @Object_ID=Object_ID,@Name=Name
    from sys.tables
    where type = 'u' 
    and object_ID > @Object_ID 
    and Name like 'Temp%' 
    order by Object_ID

    INSERT INTO @tbl (table_name, table_count)
    exec('Select ''' + @Name + ''' as TableName,count(*) AS Counter from ' + @Name)
END

SELECT * FROM @tbl

answered Dec 11, 2009 at 15:04

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

1 Comment

Phillip Senn Over a year ago

Oh, I didn't think about doing an insert in front of the exec! Thank you!

Collectives™ on Stack Overflow

While loop in Microsoft SQL Server 2008

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related