1

I have the following table:

CREATE TABLE [dbo].[Settings](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Key] [varchar](50) NOT NULL,
   [Value] [nvarchar](max) NULL,
   CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ([Id] ASC)
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I'm trying to run this query

exec sp_executesql 
   N'INSERT INTO Settings ([key], [value]) VALUES (@k6,@v6), (@k8,@v8)',
   N'@k6 nvarchar(4000), @v6 nvarchar(4000), @k8 nvarchar(4000), @v8 bit',
   @k6=N'DefaultLocale', @v6=N'eb', @k8=N'ReCaptchaIsEnabled', @v8=1

Unfortunately, I'm getting this error:

Conversion failed when converting the nvarchar value 'eb' to data type bit.

When I remove the bit definition or remove Nvarchar definition it working well. From what I see, I cannot mix different value types in this query.

Can you tell me why? Thank you

1
  • 1
    It's likely an implicit conversion issue. You might try separating the one insert into two separate inserts for the 6 and 8 parameters to see if that resolves the error. Sep 24 '20 at 15:41
3

It's the same reason this doesn't work:

DECLARE @x nvarchar(4000) = N'foo', @y bit = 1;

SELECT x = @x
UNION ALL
SELECT y = @y;

Same error. If you're going to use the wrong data types (for example, key is varchar(50), so why is @k6 nvarchar(4000)?), you're going to need to massage them to prevent unexpected errors that come from how SQL Server might construct an execution plan. Try making your statement:

INSERT INTO dbo.Settings ([key], [value])  
VALUES (@k6,CONVERT(nvarchar(max),@v6)),
       (@k8,CONVERT(nvarchar(max),@v8));
1
  • Thank you for this example!
    – Arnold
    Sep 24 '20 at 21:37
-1

It's because that causes the SQL to do the an Implicit conversion and It cause failure , here is the way SQL server engine sees your query : estimated execution plan

you have no way unless match the datatype before passing it you can find out more about CONVERT_IMPLICIT warning in Pinal blog: SQL SERVER – How to Fix CONVERT_IMPLICIT Warnings?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.