CODEWITHSUNDEEP
sqlsql-server-2008
user505210
user505210

Reputation: 1402

Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query

I have the below select statement from a stored procedure:

ALTER PROCEDURE [dbo].[Test]  
   --Params 
   @SolutionId   INT 
   ,@APIKey    varbinary(256)   
AS  
   SELECT 
       SK.SolutionID        
       ,SK.APIKey   
       ,SK.Enabled
    FROM    
       dbo.SolutionKey SK
    WHERE
       SK.SolutionID = @SolutionId 
       AND SK.APIKey = @APIKey 
       AND Enabled = 1

The issue is that SK.APIKey is a varbinary datatype but in the stored procedure from the code it is passed on as 'sampledata' and so I get the error

Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

Can someone please tell me how can I resolve this?

Upvotes: 14

Views: 70640

Answers (1)

bowlturner
bowlturner

Reputation: 2016

Something like this might work. 

ALTER PROCEDURE [dbo].[Test]  
--Params 
@SolutionId   INT 
,@APIKey    varchar(256)   
AS  

SELECT 
           SK.SolutionID        
          ,SK.APIKey    
          ,SK.Enabled
FROM    dbo.SolutionKey SK
where SK.SolutionID = @SolutionId 
  And SK.APIKey = CONVERT(VARBINARY(256), @APIKey, 1) 
  And Enabled = 1

Upvotes: 30

Related Questions