Convert VARCHAR to VARBINARY (SQL)

Question

Convert VARCHAR to VARBINARY (SQL)

Navigation

#1 by (2 votes)

2

I was creating a stored procedure in SQL Server and I missed this conversion error. "Implicit conversion from data type to varbinary is not allowed. Use the CONVERT function to run this query."

The idea is to encrypt and decrypt passwords.

CREATE PROCEDURE sp_Login @email VARCHAR(30), @pass VARCHAR(25)
AS
DECLARE @PassEncode VARBINARY(8000)
DECLARE @PassDecode  VARCHAR(25)
DECLARE @result  BIT
BEGIN
SELECT @PassEncode = contraseña FROM Usuario WHERE email = @email
SET @PassDecode = DECRYPTBYPASSPHRASE('password', @PassEncode)
END
BEGIN
IF @PassDecode = @Pass
BEGIN
SET @result=1
SELECT @result
END
ELSE
BEGIN
SET @Result=0
SELECT @result
END
END
Go

From what I read I have to use "CONVERT" but I do not know where.

sql

asked by Ccccccccc 18.07.2016 в 01:53

source

1 answer

How to know when when the request to the server has ended with Angular2 and Firebase Optimizing Jquery Code

score 2 · Accepted Answer

How it is explained in the documentation the DECRYPTBYPASSPHRASE function returns a VARBINARY but you have defined your variable PassDecode as a varchar .

You have 2 options, or make a convert of that function or modify the code to work with VARBINARY . I understand that it is always better to modify as little as possible so to do the Convert :

SET @PassDecode = CONVERT(VARCHAR(25),DECRYPTBYPASSPHRASE('password', @PassEncode), 2)

Please note that you have defined PassDecode as a varechar(25) , maybe you can fall short (or not). If it gives you a size error it increases both in its definition and in the convert the size of varchar .