Sql-server – Insert String Value To Binary(64) Column

sql serversql-server-2016varbinary

I am attempting to use a stored procedure to insert data into a table. The DDL of my table is like this:

CREATE TABLE [dbo].[data1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [fname] [varchar](100) NOT NULL,
    [lname] [varchar](100) NOT NULL,
    [phone] [varchar](10) NULL,
    [company] [varchar](100) NOT NULL,
    [email] [varchar](100) NOT NULL,
    [pass] [binary](64) NOT NULL,
    [registrationdate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[data1] ON 
GO
INSERT [dbo].[data1] ([ID], [fname], [lname], [phone], [company], [email], [pass], [registrationdate]) VALUES (1, N'Bob', N'Tomato', N'5555555555', N'Test Company', N'test1234@test.com', 0x477265656E0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000, NULL)
GO
SET IDENTITY_INSERT [dbo].[data1] OFF
GO

And this is the stored procedure that I am attempting to execute:

Declare @FName varchar(100) = 'aa'
Declare @Lname varchar(100) = 'bb'
Declare @Phone varchar(100) = 'cc'
Declare @Company varchar(100) = 'dd'
Declare @Email varchar(100) = 'ee'
Declare @Pass binary(64) = 'redsocks'


INSERT INTO dbo.appinfo (fname, lname, phone, company, email, pass) 
VALUES (@FName, @Lname, @Phone, @Company, @Email, CONVERT(VARBINARY(64), @Pass, 0))

but I get this error:

Msg 257, Level 16, State 3, Line 20
Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.

Is this statement not converting the value appropriately?

CONVERT(VARBINARY(64), @Pass, 0))

Best Answer

You need to apply the convert in the declare line, not during the insert. The error happens on the declare line, so the insert is never reached.

Related Question