CODEWITHSUNDEEP
sql-serverstored-proceduresvarbinarymax
Caspar
Caspar

Reputation: 11

Insert varbinary data in a stored procedure T-SQL

I have created a Template table in SQL Server 2014 and want to create a procedure for adding templates (Word template files) and template names. I have created the below table:

create table Template
(
    PK_TemplateID int identity(1,1) primary key,
    TemplateName nvarchar(30) not null unique,
    TemplateFile varbinary(max) not null
);

and the following code inserts a record:

INSERT INTO Template(TemplateName,TemplateFile)
   SELECT 
       'SaleTemplate.dotx' AS TemplateName,
       * 
   FROM 
       OPENROWSET(BULK N'C:\Users\User10\Desktop\example_file.dotx', SINGLE_BLOB) AS TemplateFile
GO

Now I have been running into problems when creating a procedure to insert into the Template table. This is the closest I have got:

create procedure dbo.inserttemplate
    @TemplateName varchar(30),
    @TemplateFile varchar(200)
as
    insert into Template(TemplateName, TemplateFile)
        select 
            @TemplateName as TemplateName,
            * 
        from
            OPENROWSET(bulk @templatefile, SINGLE_BLOB) AS TemplateFile

The error reads:

Incorrect syntax near '@templatefile'

but I cannot work out how to correct it. Any ideas?

Upvotes: 1

Views: 973

Answers (1)

Andomar
Andomar

Reputation: 238086

The openrowset function doesn't accept parameters, only string literals. You can work around this by creating a dynaimic SQL string and passing that to exec:

declare @sql nvarchar(max) = 'select * from openrowset(bulk ''' + 
    replace(@templatefile, ',', ',,') + ''', SINGLE_BLOB) AS TemplateFile'
exec (@sql)

Upvotes: 1

Related Questions