Processing multiple rows & display with SELECT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Processing multiple rows & display with SELECT

Author

Topic

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-22 : 14:33:56

I am processing through 5 rows, one row at a time, then displaying the row number and ProductID with the PRINT statement. This works fine:

DECLARE @counter INT, @rowValue INT
SELECT TOP 1 @rowValue=ProductID FROM Production.WorkOrder
SET @counter = 0
WHILE @counter < 5
BEGIN
SET @counter = @counter + 1
PRINT CAST(@counter AS CHAR(5)) + ' ' + CAST(@rowValue AS CHAR(5))
SELECT TOP 1 @rowValue=ProductID FROM Production.WorkOrder WHERE ProductID > @rowValue
END

Now I want to display the same data, only now I want to list column names. I figured I need a SELECT statement with two aliases (RowNumber and ProductID).

DECLARE @counter INT, @rowValue INT
SELECT TOP 1 @rowValue=ProductID FROM Production.WorkOrder
SET @counter = 0
WHILE @counter < 5
BEGIN
SET @counter = @counter + 1
SELECT CAST(@counter AS CHAR(5)) AS RowNumber, CAST(@rowValue AS CHAR(5)) AS ProductID
SELECT TOP 1 @rowValue=ProductID FROM Production.WorkOrder WHERE ProductID > @rowValue
END

This does work, but each row is listed as a separate instance because the SELECT statement appears inside of a loop. Is there a way to get all five values to display together under one instance, instead of having 5 instances listed out in my results window pane? Here

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-22 : 14:36:22

can you post sample data in consumable format along with the desired ouput??

Cheers
MIK

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-22 : 15:28:03

there are a few potential problems.

1) you do a SELECT TOP 1 with no ORDER BY Clause. The order of rows returned is not guaranteed! it will usually be in CLUSTERED INDEX order (PK usually) but it isn't guaranteed.

If you must do things this way (row by row processing). Then create a temp table or table variable and insert the processed rows into that. Then do a final select at the end.

But whatever you are doing, there is probably a better way than row by bloody row.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources