Stored procedure to insert image into SQL Server

Question

I want to store signatures in a SQL Server database table as VARBINARY(MAX). I use the following T-Sql to insert a signature:

INSERT INTO Signatures (UserID_FK, [Signature])
SELECT 2, BulkColumn
FROM OPENROWSET
(
    Bulk 'C:\Z_KB\SignatureTest.PNG', SINGLE_BLOB
) AS [Signature]

I want to turn this into a stored procedure, but it is not happy with the following:

CREATE PROCEDURE Insert_Signatures_SP
    (   
        @SignatureUri NVARCHAR(1000),
        @UserID INT
    )
AS
    INSERT INTO Signatures (UserID_FK, [Signature])
    SELECT @UserID, BulkColumn
    FROM OPENROWSET
    (
        Bulk @SignatureUri, SINGLE_BLOB
    ) AS [Signature]
GO

It is complaining that BulkColumn is invalid column name and it is looking for a string near Bulk.

I have changed it to the following, but now it says that @UserID must be declared:

CREATE PROCEDURE Insert_Signatures_SP
    (   
        @SignatureUri NVARCHAR(1000),
        @UserID INT
    )
AS
    DECLARE @sql VARCHAR(1000)

    SET @sql = 'INSERT INTO Signatures (UserID_FK, [Signature])
    SELECT @UserID , BulkColumn
    FROM OPENROWSET
    (
        Bulk '''+ @SignatureUri +''', SINGLE_BLOB
    ) AS [Signature]';

    EXEC(@sql)
GO

I have change it to the following, but it now says 'Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.':

CREATE PROCEDURE Insert_Signatures_SP
    (   
        @SignatureUri NVARCHAR(1000),
        @UserID INT
    )
AS
    DECLARE @err INT
    DECLARE @sql VARCHAR(1000)

    SET @sql = 'INSERT INTO Signatures (UserID_FK, [Signature])
    SELECT @UserID, BulkColumn
    FROM OPENROWSET
    (
        Bulk '''+ @SignatureUri +''', SINGLE_BLOB
    ) AS [Signature]';

    EXEC @err = sp_executesql @sql, N'@UserID INT', @UserID; 
    RETURN(@err);
GO

Trying to call it with:

EXEC Insert_Signatures_SP 'C:\Z_KB\SignTest.PNG', 3

If I remeber correctly, [BULK](https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver16#bulk-data_file) expects a string literal as a value of the file name parameter. A possible solution can be found [here](https://stackoverflow.com/questions/39827982/how-to-use-a-variable-in-openrowset-command). — Zhorov, Jun 12 '23 at 07:37
Execute the dynamic statement with `sp_executesql`: `DECLARE @err int; EXEC @err = sp_executesql @sql, N'@UserId INT', @UserId; RETURN (@err);` — Zhorov, Jun 12 '23 at 08:08
And finally, use the appropriate data type: `DECLARE @sql NVARCHAR(max); SET @sql = N'... statement ...'` — Zhorov, Jun 12 '23 at 08:34

score 0 · Answer 1 · answered Jun 12 '23 at 11:49

@sql must be nvarchar(max).

You should also correctly escape the URI using QUOTENAME

CREATE PROCEDURE Insert_Signatures_SP
(   
    @SignatureUri NVARCHAR(1000),
    @UserID INT
)
AS

SET NOCOUNT ON;

DECLARE @sql nvarchar(max) = '
INSERT INTO Signatures (UserID_FK, [Signature])
SELECT @UserID, BulkColumn
FROM OPENROWSET
(
    BULK ' + QUOTENAME(@SignatureUri, '''') + ', SINGLE_BLOB
) AS [Signature];
';

EXEC sp_executesql @sql,
  N'@UserID INT',
  @UserID = @UserID; 

GO

Stored procedure to insert image into SQL Server

1 Answers1

Linked