14

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user505210
  • 1,362
  • 11
  • 28
  • 50
  • @hvd, ha ha, let me also repeat the Convert one more time – radar Nov 18 '14 at 19:08
  • 2
    Well I tried doing that but had the wrong data type for the parameter so it wasn't working..I changed it to varchar as in the below helpful answer and that worked.. so I hope THE CONVERTERS(@hvd,RADAR) got a good laugh today – user505210 Nov 18 '14 at 19:16

1 Answers1

30

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
bowlturner
  • 1,968
  • 4
  • 23
  • 35