SQL Server How to output one table result from multiple results with a WHILE query

Question

From this answer: Is there a way to loop through a table variable in TSQL without using a cursor?

I'm using the method

WHILE EXISTS(SELECT * FROM #Temp)

The problem is that it's outputting multiple tables, if possible I'd like to output as a single table.

Declare @Id int

WHILE EXISTS(SELECT * FROM #Temp)
Begin

    Select Top 1 @Id = Id From #Temp

    --Do some processing here

    Delete #Temp Where Id = @Id

End

So right now it outputs this:

x  y
-- --
1  a

x  y
-- --
1  b

But I'd like it to output this:

x  y
-- --
1  a
2  b

What I'm trying to achieve, I have this in a field:

1234,1432,1235

I have a process that splits the field into records(it works with sql server 2000):

DECLARE @String VARCHAR(100)
    SELECT @String = str FROM  field --with the 1234,1432,1235

    SELECT SUBSTRING(',' + @String + ',', Number + 1,
    CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1)AS str
    INTO #temp
    FROM master..spt_values
    WHERE Type = 'P'
    AND Number <= LEN(',' + @String + ',') - 1
    AND SUBSTRING(',' + @String + ',', Number, 1) = ','
    GO

So now, #temp has:

str
---
1234
1432
1235

So I need to go through each record to query the information I need.

And I'd like it to output something like this:

str   name   age
---   ----   ---
1234  Bob    23
1432  Jay    41
1235  Tim    12

The current While loop outputs it like this, which I don't want:

str   name   age
---   ----   ---
1234  Bob    23

str   name   age
---   ----   ---
1432  Jay    41

str   name   age
---   ----   ---
1235  Tim    12

Final Working Result:

SET NOCOUNT ON;

DECLARE @String VARCHAR(1000);
SELECT @String = Tnn FROM (SELECT 
 CO.USER_2 AS Tnn
FROM 
    [VMFG].[dbo].[CUSTOMER_ORDER] AS CO 
    LEFT JOIN DBO.Tnn_Header AS Tnn ON Tnn.TnnNumber = CO.USER_2 AND Tnn.StatusID = '5' WHERE CO.ID = 'ORDERID') AS Place --with the 1234,1432,1235

DECLARE @Id nvarchar(50),
        @Discount nvarchar(50), 
        @Spin nvarchar(50), 
        @Commission_Hmm nvarchar(50), 
        @Commission nvarchar(50), 
        @TnnID nvarchar(50);

DECLARE @Output TABLE (
TnnNumber nvarchar(50),
        Discount nvarchar(50), 
        Spin nvarchar(50), 
        Commission_Hmm nvarchar(50), 
        Commission nvarchar(50), 
        TnnID nvarchar(50));

DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY
FOR  SELECT SUBSTRING(',' + @String + ',', Number + 1,
     CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1) AS [ID]
     FROM master..spt_values
     WHERE Type = 'P'
     AND Number <= LEN(',' + @String + ',') - 1
     AND SUBSTRING(',' + @String + ',', Number, 1) = ',';

OPEN crs;

FETCH NEXT
FROM  crs
INTO  @Id;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- do some processing..
SELECT 
@Id = TH.TnnNumber,
@Discount = CASE WHEN COUNT(DISTINCT TL.DiscountCodeID) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(DC.Value) AS VARCHAR(60)) END,
@Spin = CASE WHEN TS.SpinID > 4 THEN 'Has Specifics, View Tnn' ELSE TS.Value END,
@Commission_Hmm = CASE WHEN COUNT(DISTINCT TL.Commission_Hmm) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX( ISNULL(str(TL.Commission_Hmm,12),'Default Comm')) AS VARCHAR(60)) END,
@Commission = CASE WHEN COUNT(DISTINCT TL.Commission) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(ISNULL(str(TL.Commission,12),'Default Comm')) AS VARCHAR(60)) END,
@TnnID = TL.TnnID 

FROM DBO.Tnn_Header AS TH
LEFT JOIN DBO.Tnn_LINE AS TL ON TH.TnnID = TL.TnnID
LEFT JOIN DBO.Tnn_Spin AS TS ON TH.SpinID = TS.SpinID
LEFT JOIN DBO.Tnn_DiscountCode AS DC ON TL.DiscountCodeID = DC.DiscountCodeID 

WHERE TnnNumber = @id

GROUP BY 
TH.TnnNumber,
TS.SpinID,
TS.Value,
TL.TnnID
-- end do some processing..
    INSERT INTO @Output (TnnNumber, Discount, Spin, Commission_Hmm, Commission, TnnID)
    VALUES (@Id, @Discount, @Spin, @Commission_Hmm, @Commission, @TnnID);

    FETCH NEXT
    FROM  crs
    INTO  @Id;
END;

CLOSE crs;
DEALLOCATE crs;

SELECT TnnNumber, Discount, Spin, Commission_Hmm, Commission, TnnID
FROM   @Output;

try to add order by on a unique field in your select statement — FLICKER
– FLICKER, Commented Feb 18, 2016 at 0:51
@inquisitive_mind without the INTO #temp2 it outputs like right below the "so right now it puts this:" — sojim2
– sojim2, Commented Feb 18, 2016 at 0:57
You will have to insert the modified column data into another temp table and then outside the WHILE print it out. — nobody
– nobody, Commented Feb 18, 2016 at 0:58

Solomon Rutzky · Accepted Answer · 2016-02-18 15:41:33Z

4

You are wasting your time and energy following such bad advice. If you absolutely must (extra emphasis on the must) take a row-by-row approach (CURSOR or WHILE loop), then you are better off with a CURSOR. It is a built-in construct that is more efficient, and less error-prone. You just need to use the right options, such as making it STATIC, LOCAL, READ_ONLY, and FORWARD_ONLY. You don't need STATIC if the cursor query is only hitting temporary tables and/or table variables.

People will argue with this and say that "you must avoid cursors at all cost!", but they haven't done the tests to see that such a popular notion is really just a myth. And if they have done tests that appear to confirm it, then they haven't set the appropriate options, mostly STATIC, which dumps the result of the cursor query into a temp table. Without this option, fetching new rows will re-check the base tables to make sure that they still exist, and that is where the performance hit is (the I/O plus the locking). And that is also why you typically don't need the STATIC option when querying only temporary tables and/or table variables. What do I mean by "re-checking"? Just look at the documentation for @@FETCH_STATUS. The return values don't just cover "success" (0) and "no more rows" (-1): there is a return value, (-2), that means "The row fetched is missing".

SET NOCOUNT ON;
DECLARE @Id INT,
        @Name sysname,
        @Type VARCHAR(5);

--  the Table Variable replaces #Temp2 in the original query
DECLARE @Output TABLE (Id INT NOT NULL, Name sysname, [Type] VARCHAR(5));

-- the CURSOR replaces #Temp in the original query
DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY
FOR  SELECT [object_id], name, [type]
     FROM   sys.objects -- dbo.sysobjects for SQL 2000 -- ATable in the original query
    ORDER BY [object_id] ASC;

OPEN crs;

FETCH NEXT
FROM  crs
INTO  @Id, @Name, @Type;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    INSERT INTO @Output (Id, Name, [Type])
    VALUES (@Id, @Name, @Type);

    -- do some processing..

    FETCH NEXT -- replaces the DELETE and re-SELECT in the original query
    FROM  crs
    INTO  @Id, @Name, @Type;
END;

CLOSE crs;
DEALLOCATE crs;

SELECT Id, Name, [Type]
FROM   @Output;

UPDATE

Given the iteration is being done over a query that splits a CSV of INTs, the resulting query would look similar to the following:

SET NOCOUNT ON;

DECLARE @String VARCHAR(1000);
SELECT @String = str FROM [Table]; --with the 1234,1432,1235

DECLARE @Id INT,
        @Name NVARCHAR(50),
        @Age  TINYINT;

DECLARE @Output TABLE (Id INT NOT NULL, Name NVARCHAR(50), Age TINYINT);

DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY
FOR  SELECT SUBSTRING(',' + @String + ',', Number + 1,
     CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1) AS [ID]
     FROM master..spt_values
     WHERE Type = 'P'
     AND Number <= LEN(',' + @String + ',') - 1
     AND SUBSTRING(',' + @String + ',', Number, 1) = ',';

OPEN crs;

FETCH NEXT
FROM  crs
INTO  @Id;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- do some processing..
    -- Logic to set value of @Name
    -- Logic to set value of @Age

    INSERT INTO @Output (Id, Name, Age)
    VALUES (@Id, @Name, @Age);

    FETCH NEXT
    FROM  crs
    INTO  @Id;
END;

CLOSE crs;
DEALLOCATE crs;

SELECT Id, Name, Age
FROM   @Output;

edited Feb 18, 2016 at 15:41

answered Feb 18, 2016 at 1:53

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

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

19 Comments

sojim2 Over a year ago

Thank you for the explanation, I'll give this one a try too, hopefully it'll work with sql server 2000

sojim2 Over a year ago

where would i put the temp table?

Solomon Rutzky Over a year ago

@sojim2 I don't see why it wouldn't work in SQL Server 2000. It has been a while since I used that version, but I remember doing this back then. About the temp table, are you referring to #Temp? If so, then you don't need it at all in this approach. Technically, the system-created / hidden table created to hold the results of the cursor query due to the STATIC option, is the equivalent of #Temp. If it helps, I updated the example code in my answer to note where each piece fits from the original query. I just wanted to provide a working example.

sojim2 Over a year ago

But #temp has the data I need to process? Or am I not understanding something? I just tried the code with just the processing code (no #temp table with the data i need to process) and it's giving some errors such as "invalid column name 'object_id' also sys.objects may not work with sql server 2000

Solomon Rutzky Over a year ago

@sojim2 I just updated with the correct system table for SQL Server 2000 (i.e. dbo.sysobjects). Regarding #Temp, where did you get the data to populate #Temp in the first place? And why did you create #Temp in the first place? If it was to accomplish the suggestion from that other post, then that is part of what is wasteful. Whatever query was used to populate #Temp is the query that is used in the CURSOR definition (after the FOR).

|

FLICKER · Accepted Answer · 2016-02-18 01:18:25Z

1

your query has syntax error but I tried below query and worked fine

-- this is only to populate my data table
Select object_id Id, name Into #Temp From sys.tables

select * into #temp2 from #Temp where 1=2

Declare @Id int

WHILE EXISTS(SELECT * FROM #Temp)
Begin
    Select Top 1 @Id = Id
    From #Temp
    ORDER BY Id -- this order is important

    -- use insert...into, NOT select...into
    insert into #temp2 
    select * 
    from #Temp
    where Id = @Id

    Delete #Temp Where Id = @Id
End

BTW, you can not have SELECT...INTO inside a loop, as the 2nd iteration will raise error. You need to create #temp2, out side the loop and use INSERT...INTO instead of SELECT...INTO

edited Feb 18, 2016 at 1:18

answered Feb 18, 2016 at 0:57

FLICKER

6,7434 gold badges52 silver badges83 bronze badges

10 Comments

nobody Over a year ago

Missing select * from #temp2 outside the WHILE?

FLICKER Over a year ago

@inquisitive_mind, no that part is only to populate my sample data table. I think the missing part is the ORDER BY. I added some comments, please take a look

FLICKER Over a year ago

if this is the solution, pleas mark as answer. thanks

Kidiskidvogingogin Over a year ago

Just a thought. @FLICKER You could also just do an empty select to create the #temp2 table i.e. SELECT * FROM #Temp INTO #temp2 WHERE 1 = 2 That way you don't have to know the schema of #Temp before you create the #temp2 table

FLICKER Over a year ago

@Kidiskidvogingogin, right, that is a way to create a temp table from existing one. I edit my post

|

Collectives™ on Stack Overflow

SQL Server How to output one table result from multiple results with a WHILE query

2 Answers 2

19 Comments

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

19 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related