CODEWITHSUNDEEP
sqlsql-servert-sql
mjyazdani
mjyazdani

Reputation: 2035

how to use select expression in while loop in sql server?

I need to use a select expression in a while loop and I use the below sample code: 

 declare @i integer
 set @i=1
 while (@i<10)
 begin
   select @i as m;
   set @i=@i+1
 END

this code returns 10 separate table! I want it to return all select results in one table... is that possible? if yes... how?

Upvotes: 1

Views: 24262

Answers (4)

andrew
andrew

Reputation: 91

squillman got it...with #t (create table # - table persisted at top-level scope while session is open - if you have several batch statements, you can reference this table in any after declaration until you drop the table)

Cris also got it with @test (declare @ table - variable - only persisted in the current scope - single execution batch block...note that there are several performance issues that can be introduced if you use this)

the last type of temp table you can use is a global temp table (create table ## - lasts as long as the session that created it stays open or until it is dropped)

with #t, you may want to add this to the beginning of your script if you don't close the session:

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t

Enjoy the temp tables!

Upvotes: 2

Cris
Cris

Reputation: 13351

declare @i integer
DECLARE @test TABLE(
  m /*your data type*/
)
 set @i=1
 while (@i<10)
 begin
   insert into @test select @i;

   set @i=@i+1
 END 

select * from @test

Upvotes: 1

squillman
squillman

Reputation: 13641

You can use a temp table or table variable for this.

Here's how to do it using a temp table.

CREATE TABLE #t (m INT)
DECLARE @i INT
SET @i=1
WHILE (@i<10)
BEGIN
  INSERT INTO #t SELECT @i
  SET @i=@i+1
END 
SELECT m FROM #t

Very similar with a table variable

DECLARE @t TABLE (m INT)
DECLARE @i INT
SET @i=1
WHILE (@i<10)
BEGIN
  INSERT INTO @t SELECT @i
  SET @i=@i+1
END 
SELECT m FROM @t

Upvotes: 5

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

It is not possible. Each SELECT statement generates its own result set. You can use temp table to add results of each iteration and then get all in one table. To generate sequence of integers you can use this (for SQL SERVER 2005 + )

;WITH CTE
AS
(
   SELECT 1 N
   UNION ALL
   SELECT N + 1 FROM CTE
   WHERE N<10
)
SELECT N FROM CTE

Upvotes: 4

Related Questions