user505210 user505210 - 2 months ago 21
SQL Question

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?

Answer

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
Comments