sql - 如何将多个pdf文件保存到sql server中的一个文件夹中

Question

我的数据库中有很多 pdf 文件，我想根据优先级将它们存储在一个文件夹中。当我使用下面的代码时，我无法保存所有文件；它只保存一个文件。 :(

Declare 
            @File_Path_1 VARBINARY(MAX),
            @FileName_1 VARCHAR(MAX),
            @Timestamp_1 varchar(max),
            @objectToken_1 int

DECLARE cFiles CURSOR FAST_FORWARD FOR
SELECT request_number from data where priority=1
OPEN cFiles

FETCH NEXT FROM cFiles INTO @FileName_1

WHILE @@FETCH_STATUS = 0 BEGIN
   SET @FileName_1 = @FileName_1
    set @Timestamp_1='c:\Dump\1.pdf'
    EXEC sp_oacreate 'ADODB.Stream', @ObjectToken_1 OUTPUT
    EXEC sp_oasetproperty @objecttoken_1, 'type', 1
    EXEC sp_oamethod @objecttoken_1,'open'
    EXEC sp_oamethod @objecttoken_1,'Write',Null,@File_Path_1
    EXEC sp_oamethod @objecttoken_1,'Savetofile',Null,@Timestamp_1,2
    EXEC sp_oamethod @objecttoken_1,'Close'
    EXEC sp_oamethod @objecttoken_1,'open'

    FETCH NEXT FROM cFiles INTO @FileName_1
END

CLOSE cFiles
DEALLOCATE cFiles

上面的代码只获取了一条记录，请大家帮忙

Answer 1

DECLARE @SOURCEPATH VARBINARY(MAX),
@DESTPATH VARCHAR(MAX),
@ObjectToken INT,
@Request_Number varchar(30)

DECLARE PDFPATH CURSOR FAST_FORWARD FOR
SELECT ID from Table 
OPEN PDFPATH
FETCH NEXT FROM PDFPATH INTO @SOURCEPATH, @Request_Number 

WHILE @@FETCH_STATUS = 0
BEGIN

SET @DESTPATH = 'C:\path' + CAST(@Request_Number AS varchar) + '.pdf'
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @SOURCEPATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @DESTPATH, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken

FETCH NEXT FROM PDFPATH INTO @SOURCEPATH, @Request_Number
END

我希望这个片段可以帮助有需要的人:)谢谢大家帮助我:)