从图像列导出多个图像

I have the following stored procedure, how could I modify it to export multiple images at once? A TicketID can contain several images, could the IN statement work here? Appreciate all answers!

我有以下存储过程，如何修改它以一次导出多个图像？ TicketID可以包含多个图像，IN语句可以在这里工作吗？感谢所有答案！

 CREATE PROCEDURE dbo.ExportImage (
 @ImgName NVARCHAR (100)
,@ImageFolderPath NVARCHAR(1000)
,@Filename NVARCHAR(1000)
)
AS
BEGIN

   DECLARE @ImageData VARBINARY (max);
   DECLARE @Path2OutFile NVARCHAR (2000);
   DECLARE @Obj INT

   SET NOCOUNT ON

   SELECT @ImageData = (
     SELECT convert (VARBINARY (max), ImgData, 1)
     FROM Images
     WHERE TicketID = @ImgName
     );

   SET @Path2OutFile = CONCAT (
     @ImageFolderPath
     ,'\'
     , @Filename
     );
 BEGIN TRY
 EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT;
 EXEC sp_OASetProperty @Obj ,'Type',1;
 EXEC sp_OAMethod @Obj,'Open';
 EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData;
 EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2;
 EXEC sp_OAMethod @Obj,'Close';
 EXEC sp_OADestroy @Obj;
END TRY

BEGIN CATCH
EXEC sp_OADestroy @Obj;
END CATCH

SET NOCOUNT OFF
END

This is the query I execute:
exec dbo.ExportImage '2042','C:\ExpImg','2042.jpg'

这是我执行的查询：exec dbo.ExportImage'2042'，'C：\ ExpImg'，'2042.jpg'

Errormsg:

ERRORMSG：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.

子查询返回的值超过1。当子查询遵循=，！=，<，<=，>，> =或子查询用作表达式时，不允许这样做。

1 个解决方案

#1

Something like that:

像这样的东西：

CREATE PROCEDURE dbo.ExportImage (
 @ImgName NVARCHAR (100)
,@ImageFolderPath NVARCHAR(1000)
,@Filename NVARCHAR(1000)
)
AS
BEGIN

   DECLARE @ImageData VARBINARY (max);
   DECLARE @Path2OutFile NVARCHAR (2000);
   DECLARE @Obj INT

   SET NOCOUNT ON

DECLARE @cursor CURSOR
SET @cursor = CURSOR FOR
    SELECT convert (VARBINARY (max), ImgData, 1)
     FROM Images
     WHERE TicketID = @ImgName
OPEN @cursor
FETCH NEXT
FROM @cursor INTO @ImageData
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @Path2OutFile = CONCAT (
     @ImageFolderPath
     ,'\'
     , @Filename
     );
 BEGIN TRY
 EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT;
 EXEC sp_OASetProperty @Obj ,'Type',1;
 EXEC sp_OAMethod @Obj,'Open';
 EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData;
 EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2;
 EXEC sp_OAMethod @Obj,'Close';
 EXEC sp_OADestroy @Obj;
END TRY

BEGIN CATCH
EXEC sp_OADestroy @Obj;
END CATCH

FETCH NEXT
FROM @cursor INTO @ImageData
END
CLOSE @cursor
DEALLOCATE @cursor

END