使用存储过程将文件插入到SQL Server而不使用前端

I am trying to insert file through SQL. I use following query.

我试图通过SQL插入文件。我使用以下查询。

INSERT INTO [dbo].[Attachments] (FileName, FileBinary)
    SELECT 
        'non-date-in-sql-server-column', 
        BulkColumn 
    FROM 
        OPENROWSET(Bulk 'C:\Users\Pictures\Picture.JPG', SINGLE_BLOB) AS BLOB

It's working fine.

它工作正常。

I want to write the procedure that take dynamic path. Its giving me error that I cannot take Filebinary in addin. Which is datatype varbinary. What is the best way to do ?

我想编写采用动态路径的过程。它给了我错误,我不能把文件箱加入。哪个是数据类型varbinary。什么是最好的方法?

I have done following but its not taking properly binary value.

我已经完成了以下但是没有采用正确的二进制值。

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString = 'SELECT ' + '''' +@Filename +'''' + ' AS Name,' +  'FileBinary

FROM OPENROWSET(BULK N''' + @ImagePath + ''',SINGLE_BLOB) AS FileBinary(FileBinary);'

Insert Into Attachments (ApplicantID, FileName, FileBinary)
Values (@ApplicantID, @FileName, Convert(varbinary(max), @SQLString))

1 个解决方案

#1

Put the Insert statement inside a dynamic query and execute it.

将Insert语句放在动态查询中并执行它。

Now your @SQLString will not have the FileBinary value it will have the dynamically framed string . You need to execute it to get the values

现在你的@SQLString将没有FileBinary值,它将拥有动态框架字符串。您需要执行它来获取值

DECLARE @SQLString   NVARCHAR(MAX),
        @Filename    VARCHAR(500), -- Pass file name here
        @ApplicantID VARCHAR(500) --Pass Application ID here

SET @SQLString = '
    Insert Into Attachments
    (
            ApplicantID,
            FileName,
            FileBinary
    )
    SELECT @ApplicantID,@Filename,FileBinary
    FROM OPENROWSET(BULK N''' + @ImagePath
                 + ''',SINGLE_BLOB) AS FileBinary(FileBinary);'

EXEC Sp_executesql
  @SQLString,
  N'@Filename varchar(500),@ApplicantID varchar(500)',
  @Filename =@Filename,
  @ApplicantID=@ApplicantID

#1