1

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))
  • @AaronBertrand - that got it. Thank you so much! – MitchMahoney Nov 23 '18 at 4:00
1

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.

| improve this answer | |

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.