Conversion failed when converting the varchar value Error in SQL

Question

I am trying to create a dynamic SQL query and perform insert operation. But for some reason, I am getting error: Conversion failed when converting the varchar value '

Please can anyone help me in resolving this error. Getting error at values('@file_type_id'

Below is my SQL code:

    DECLARE @file_name VARCHAR(100) = 'MyFile'
    DECLARE @file_type_id INT = 1
    DEclare @filing_id bigint = 57
    DECLARE @created_at DATETIME = GETDATE()
    DECLARE @created_by BIGINT = 2
    DECLARE @is_confidential bit = 1

    set @insertquery = 
    '
    DECLARE @Document AS VARBINARY(MAX)
    SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK ''C:\SampleTestFiles\MyWordDoc.doc'', SINGLE_BLOB ) AS Doc

    Insert  ['+@databasename+'].['+@schemaname+'].['+@tablename+'] 
          ( [file_type_id], [file],  [file_name], [filing_id], [created_at], [created_by], [is_confidential])
   values ( '@file_type_id', @Document, @file_name, @filing_id , @created_at, @created_by, @is_confidential)
   '
    exec (@insertquery)

'['+@databasename+'] isn't injection safe! Use QUOTENAME. Also, 'values ( '@file_type_id', ... isn't correct. you should be parametrising the value of @file_type_id — Thom A
– Thom A ♦, Commented Nov 21, 2019 at 14:38
Because you are building up a string which contains all the string literals, not the values of your variables. You can pass parameters to dynamic sql but you need to use sp_executesql. — Sean Lange
– Sean Lange, Commented Nov 21, 2019 at 14:39
but mainly you are not concatenating '@file_type_id' - try ' + @file_type_id + ' — smoore4
– smoore4, Commented Nov 21, 2019 at 14:42
After doing ' + @file_type_id + ' it is still giving same error — user2961127
– user2961127, Commented Nov 21, 2019 at 14:45
@file_type_id is an int so the rest of your statment will be implicitly cast to an nvarchar(MAX), but ' + @file_type_id + ' is also not the correct approach. — Thom A
– Thom A ♦, Commented Nov 21, 2019 at 14:45

Thom A · Accepted Answer · 2019-11-21 15:28:39Z

3

I suspect this is what you are after. Note that I have parametrised the variables, and safely quoted the values of your dynamic objects:

DECLARE @databasename sysname,
        @schemaname sysname,
        @tablename sysname;

DECLARE @file_name varchar(100) = 'MyFile',
        @file_type_id int = 1,
        @filing_id bigint = 57,
        @created_at datetime = GETDATE(),
        @created_by bigint = 2,
        @is_confidential bit = 1,
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10),
        @insertquery nvarchar(MAX);

SET @insertquery = N'DECLARE @Document AS VARBINARY(MAX);' + @CRLF +
                   N'SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK ''C:\SampleTestFiles\MyWordDoc.doc'', SINGLE_BLOB ) AS Doc;' + @CRLF + @CRLF +
                   N'INSERT INTO ' + QUOTENAME(@databasename) + '.' +  QUOTENAME(@schemaname) + N'.' +  QUOTENAME(@tablename) + N'  ( [file_type_id], [file],  [file_name], [filing_id], [created_at], [created_by], [is_confidential])' + @CRLF + 
                   N'VALUES (@file_type_id, @Document, @file_name, @filing_id , @created_at, @created_by, @is_confidential);';

--PRINT @insertquery; --Your best friend.

EXEC sp_executesql @insertquery, N'@file_name varchar(100),@file_type_id int,@filing_id bigint,@created_at datetime, @created_by bigint,@is_confidential bit', @file_name, @file_type_id, @filing_id, @created_at, @created_by, @is_confidential;

I can't test the above, however, if you get any errors I would suggest looking at your best friend. if, however, I use the declarations below...:

DECLARE @databasename sysname = N'MyDB',
        @schemaname sysname = N'dbo',
        @tablename sysname = N'MyTable';

I get this statement, which appears to be correct:

DECLARE @Document AS VARBINARY(MAX);
SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK 'C:\SampleTestFiles\MyWordDoc.doc', SINGLE_BLOB ) AS Doc;

INSERT INTO [MyDB].[dbo].[MyTable]  ( [file_type_id], [file],  [file_name], [filing_id], [created_at], [created_by], [is_confidential])
VALUES (@file_type_id, @Document, @file_name, @filing_id , @created_at, @created_by, @is_confidential);

Edit: to address the comment from the OP "How do I put another variable for file path." the correct syntax of that would be to make use of the second parameter from QUOTENAME (note that this strongly assumes that @filepath can never have a length longer than 128 characters):

SET @insertquery = N'DECLARE @Document AS VARBINARY(MAX);' + @CRLF +
                   N'SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK ' + QUOTENAME(@filepath,'''') + N', SINGLE_BLOB ) AS Doc;' + @CRLF + @CRLF +
                   N'INSERT INTO ' + QUOTENAME(@databasename) + '.' +  QUOTENAME(@schemaname) + N'.' +  QUOTENAME(@tablename) + N'  ( [file_type_id], [file],  [file_name], [filing_id], [created_at], [created_by], [is_confidential])' + @CRLF + 
                   N'VALUES (@file_type_id, @Document, @file_name, @filing_id , @created_at, @created_by, @is_confidential);';

edited Nov 21, 2019 at 15:28

answered Nov 21, 2019 at 14:45

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Sean Lange Over a year ago

Wow somebody had their coffee today. Well done and fast like lightning!!!

Thom A Over a year ago

It's much later in the day for me that yourself, @SeanLange . I've even been back from my lunch break for a couple of hours. :) Plus, I'm foolish enough to enjoy a dynamic SQL "challenge".

Gordon Linoff Over a year ago

@Larnu . . . I am curious why you are manually inserting a @CRLF instead of just having a string span multiple lines -- which SQL Server supports. Do you have a particular reason? Or is this just the way that you write multiline strings?

Thom A Over a year ago

I do, @GordonLinoff. Why I define my Carriage Returns and Line Breaks as variables in Dynamic SQL

Sean Lange Over a year ago

hahaha only about 6 hours later. I was about to post a very similar answer but saw yours. Between you and Gordon it is amazing I ever manage to get anything posted. :D

|

Collectives™ on Stack Overflow

Conversion failed when converting the varchar value Error in SQL

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related