Link to home
Start Free TrialLog in
Avatar of Richard Lloyd
Richard Lloyd

asked on

MS SQL Stored Proededure declaring variable error

I have found a very useful SQL stored procedure online that exports images from a database to create a file.


The code is:


CREATE PROCEDURE dbo.usp_ExportImage (
   @PicName 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), PictureData, 1)
         FROM Pictures 
         WHERE pictureName = @PicName
         );
 
   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

Open in new window

 to call the procedure, you need to pass the command the following variables

exec dbo.usp_ExportImage 'DRAGON','C:\MyPictures\Output','Dragon.jpg' 

Open in new window

I need to add the table name ('Pictures' in the example above as a variable), so have added the variable as below, using 'TableName as the table variable


exec dbo.usp_ExportImage 'TableName','DRAGON','C:\MyPictures\Output','Dragon.jpg' 

Open in new window

and then changes the stored procedure to

 

CREATE PROCEDURE dbo.usp_ExportImage (
    @PicTable NVARCHAR(100)
   ,@PicName 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), PictureData, 1)
         FROM @PicTable
         WHERE pictureName = @PicName
         );
 
   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

Open in new window

However when I run the command I get the following error 


Msg 1087, Level 16, State 1, Procedure sp_ExportImage, Line 17 [Batch Start Line 0]
Must declare the table variable "@PicTable".

Open in new window

If anyone can point out where I am going wrong I'd be very grateful.


The original source code is available at 

https://www.mssqltips.com/sqlservertip/4963/simple-image-import-and-export-using-tsql-for-sql-server/


NB If you install, beware of the privileges settings contained in the article (Which I have done)

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Richard Lloyd
Richard Lloyd

ASKER

Thank you.


So I'd leave the code the same except update to add the @SQL and EXEC(SQL)


CREATE PROCEDURE dbo.usp_ExportImage (
    @PicTable NVARCHAR(100)
   ,@PicName NVARCHAR (100)
   ,@ImageFolderPath NVARCHAR(1000)
   ,@Filename NVARCHAR(1000)
   )
AS
BEGIN
   DECLARE @SQL varchar(max) = '';
   DECLARE @ImageData VARBINARY (max);
   DECLARE @Path2OutFile NVARCHAR (2000);
   DECLARE @Obj INT
 
   SET NOCOUNT ON
 
   SET @SQL = 'SELECT @ImageData = (
         SELECT convert (VARBINARY (max), PictureData, 1)
         FROM '+@PicTable+'
         WHERE pictureName = @PicName
         )';


exec (@SQL)


  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

Open in new window


 


  

um, sure?

I don't know SQL Server code all that well and I don't have a system where I can try it.  I could probably mock up a fiddle on one of the websites to prove the concept but you could test it faster than I could.

Should either work or not work, right?

I'll give it a go.


Thanks for your help?