CODEWITHSUNDEEP
sql.netsql-serverstored-proceduresparameters
MaazKhan47
MaazKhan47

Reputation: 849

The data types varchar and varbinary(max) are incompatible in the add operator

I am trying to insert into a FileTable using a stored procedure where my FileStream, FileName, FilePath will be parametrized. I have tried this but it throws an error

The data types varchar and varbinary(max) are incompatible in the add operator

My stored procedure:

    @filePath VARCHAR(100),
    @fileName VARCHAR(100)
AS
BEGIN
    DECLARE @file VARBINARY(MAX)
    DECLARE @sql NVARCHAR(max)

    SET @sql = 'SELECT'+ @file +' = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK '+ @filePath +', SINGLE_BLOB) AS x'

    EXEC(@Sql);

    INSERT INTO dbo.FileStore(name, file_stream)
        SELECT @fileName, @file
END

Upvotes: 3

Views: 12065

Answers (1)

under
under

Reputation: 3077

The main issue is with @file variable inside your dynamic SQL. 

SET @sql = 'SELECT'+ @file +' = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK '+ @filePath +', SINGLE_BLOB) AS x'

When this is executed the @file variable will be replaced with the content of the variable. So if @file contains empty string (''), this will be resolved to:

SELECT  = CAST(bulkcolumn....

If @file is NULL the whole string will be NULLed..

If you need to use variables inside dynamic SQL, that is pass a value to the statement inside dynamic SQL or retrieve a value from it, you need to use sp_executeSQL and @params. Below is fully worked out and tested example. The @file variable inside the dynamic SQL statement is "mapped" to the @file variable in your stored procedure. sp_executeSQL knows to populate your @file variable with whatever is inside the @file variable in dynamic SQL. Note that even though these are named the same, they are different variables. If you are coming from .Net, dynamic SQL is like a method in .net, it has its own scope for variables. If it would make things clearer, you can use a different name for @file variable inside dynamic SQL. 

declare @filePath VARCHAR(100) = 'insert full path'
     , @fileName VARCHAR(100) = 'insert file name';
DECLARE @file VARBINARY(MAX);
DECLARE @sql NVARCHAR(max);
declare @params nvarchar(max);


set @sql = N'SET @file = (SELECT BulkColumn FROM OPENROWSET(BULK N''' + @filePath + ''', SINGLE_BLOB) AS x)';
set @params = N'@file varbinary(max) OUTPUT';

print @sql;

EXEC sp_executesql @sql, @params, @file = @file OUTPUT;
select @file;

Upvotes: 2

Related Questions