Type mismatch in Stored Procedure

I have the following Stored Procedures to encrypt and decrypt passwords, which are a varchar(255)

To encrypt:

USE [BlueDot]
GO

/****** Object:  StoredProcedure [dbo].[getEncryptedString]    Script Date: 11/18/2015 15:43:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getEncryptedString] 
	@LoginId varchar(max)
as begin

/*	create master key encryption by password ='mystrongpassword';

	create certificate bluedot with subject = 'bluedot';

	create symmetric key bdotpassword with algorithm=triple_des encryption by certificate bluedot;
*/
	exec OpenKeys;
	
	declare @result varbinary( 256 );
	set @result = dbo.Encryptbdot( @LoginId );
	
	select @result;
	
end
GO

Open in new window


To derypt:

USE [BlueDot]
GO

/****** Object:  StoredProcedure [dbo].[getDecryptedString]    Script Date: 11/18/2015 15:44:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getDecryptedString] 
	@LoginIdenctypted varbinary(128)
as begin

/*	create master key encryption by password ='mystrongpassword';

	create certificate bluedot with subject = 'bluedot';

	create symmetric key bdotpassword with algorithm=triple_des encryption by certificate bluedot;
*/
	exec OpenKeys;
	
	declare @result varchar(max);
	set @result = dbo.Decryptbdot( @LoginIdenctypted );
	
	select @result;
	
end
GO

Open in new window


Then I try to run them to test. I test encrypt:

exec getEncryptedString 'demo';

And I get a long string, which makes me think it works fine.

I run the decrypt function using the resulting string from the above, which should give me 'demo', but instead I get an error, this is my code to decrypt:

exec dbo.getDecryptedString '0x00C4C84EF9132F4DB004EE741XXX0486CD02AF4B7B31EBXXX978645D';

This is the error:

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

I need to fix the Decrypt script to make it work ... any ideas what may be wrong in the Stored Procedure ?
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Try this as it looks like the output from first SP is binary as per your code above:
<<
...
declare @result varbinary( 256 );
      set @result = dbo.Encryptbdot( @LoginId );
select @result;
...
>>

declare @param varbinary;
set @param = cast('0x00C4C84EF9132F4DB004EE741XXX0486CD02AF4B7B31EBXXX978645D' as varbinary);

exec dbo.getDecryptedString @param
0
AleksAuthor Commented:
I get null
0
lcohanDatabase AnalystCommented:
You need to be consistent in your variables datatype and length first of all and please check that both SP's datatype length is the same and the parameter I put as an example.

in decrypt SP @LoginIdenctypted varbinary(128) but in the encrypt SP the @result varbinary( 256 );

other than that you need to check the dbo.Encryptbdot to see why it returns NULL.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
I changed it to 256 but still get the same error.
What happens if I change the data type in my database from varchar(100) to varbinary(256)  
then have varbinary in both stored procedures ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.