SQL Server Loop through query results

hc8w905p 于 2023-03-22 发布在其他

关注(0)|答案(2)|浏览(116)

DECLARE @files TABLE (DocumentID Varchar(50))

INSERT INTO @files (DocumentID)
SELECT DISTINCT D.DocumentID
FROM Documents D
WHERE D.DocumentID IN ('637542', '655437', '655900') --example for debugging

-- loop
DECLARE @i INT = 1
DECLARE @rowCount INT = (SELECT COUNT(*) FROM @files)

WHILE @i <= @rowCount
BEGIN
   DECLARE @id Varchar(50) = (SELECT DocumentID FROM @files WHERE ROW_NUMBER() = @i)
   EXEC dbo.bom @docuid=@id
   SET @i = @i + 1
END

This code gives me the following errors:

Msg 10753, Level 15, State 3, Line 19
The function 'ROW_NUMBER' must have an OVER clause.
Msg 137, Level 15, State 2, Line 20
Must declare the scalar variable "@id".

How can I declare an OVER clause if I'm using @i there and what does exactly the scalar variable message mean?

As the procdeure name might suggest, it prints a bill of materials for the selected docid. I'd like to print boms of multiple files to one dataset.

When executing the procedure once, it looks like this:
| docuid | description | etc |
| ------------ | ------------ | ------------ |
| 637542 | value a | value x |

What I need, looks like this:

docuid	description	etc
637542	value a	value x
355437	value b	value y
655900	value c	value z

sql-server

来源：https://stackoverflow.com/questions/75788299/loop-through-query-results

2条答案

按热度按时间

zd287kbt1#

DECLARE @InDocumentID VARCHAR(50);
SET @InDocumentID='';
DECLARE cCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
   SELECT DISTINCT D.DocumentID
   FROM Documents AS D
   WHERE D.DocumentID IN ('637542', '655437', '655900');
OPEN cCursor;
FETCH NEXT FROM cCursor INTO @InDocumentID;
WHILE @@FETCH_STATUS=0
BEGIN
  EXEC dbo.bom @docuid=@InDocumentID;
  FETCH NEXT FROM cCursor INTO @InDocumentID;
END
CLOSE cCursor;
DEALLOCATE cCursor;

I would try standard cursor approach

赞(0）回复(0）举报 2023-03-22

pw9qyyiw2#

If you want to use the same style but make it work, you need to add your own index/row column to @files (for example, via IDENTITY ).

DECLARE @files TABLE (RN INT IDENTITY(1,1) PRIMARY KEY CLUSTERED , DocumentID Varchar(50))

INSERT INTO @files (DocumentID)
SELECT DISTINCT D.DocumentID
FROM Documents D
WHERE D.DocumentID IN ('637542', '655437', '655900') --example for debugging

-- loop
DECLARE @i INT = 1
DECLARE @rowCount INT = (SELECT COUNT(*) FROM @files)

DECLARE @id Varchar(50)
WHILE @i <= @rowCount
BEGIN
    SET @id = (SELECT DocumentID FROM @files WHERE RN = @i)
   EXEC dbo.bom @docuid=@id
   SET @i = @i + 1
END

赞(0）回复(0）举报 2023-03-22

我来回答

SQL Server Loop through query results

2条答案

相关问题

热门标签

最新问答