CODEWITHSUNDEEP
sql-serverprintingwhile-loop
TheTechGuy
TheTechGuy

Reputation: 17374

While loop without a counter in SQL - help

I am running the following code. The code does not loop through all the records that are there in the @result. What is the correct syntax. Do I have to use counter in this case? I want it to be flexible so it print all values that are there in the variable.

declare @result varchar(1000)
select @result = item from items

while @result != ''
begin
print @result
end

Select item from items

result in this query

enter image description here

What I am getting from print is an endless loop that I have to manually stop and it prints this ...

small bed
small bed
small bed
small bed

What is decent way to print all values in the variable. I am talking about text data only, not numbers.

Upvotes: 1

Views: 1206

Answers (3)

Craig
Craig

Reputation: 554

You could use a cursor to iterate over all of the data returned from your query.

declare a_cursor cursor for select Item from Items
declare @Item varchar(1000)

open a_cursor
fetch next from a_cursor into @Item
while @@fetch_status = 0
begin
     print @Item
     fetch next from a_cursor into @Item
end

close a_cursor
deallocate a_cursor

Upvotes: 0

Derek
Derek

Reputation: 23278

This might be difficult to explain, because it appears you're missing a fundamental understanding of the way SQL/databases work.

The code you wrote can only store a single value of the item column from the items table. Think of a varchar(1000) as the equivalent of a string in your favorite procedural language. The select @result = ... statement is essentially going to each row and setting @result to item, which means @result keeps getting replaced with the next value (and, upon conclusion of the statement, it's contents will be the last value in the table).

In general, you shouldn't be using loops in SQL code. That is best left for whatever front-end application needs the data. Of course, there are exceptions.

I would strongly suggest reading a primer on SQL, particularly on set-based operations vs procedural operations.

JNK's solution may give you what you're looking for in this instance, but I would question what exactly you would need that type of solution for in the first place.

Upvotes: 2

JNK
JNK

Reputation: 65187

SET @result = ''

SELECT @result = @result + CHAR(10) + CHAR(13) + Item from Items

PRINT @Result

That's all you need. The WHILE loop is superfluous.

Upvotes: 3

Related Questions