Problem with SUM() with varbinary data

Question

Hello everybody,

I have a problem, my information in BD is encrypted. but i need to desencryte and convert to numeric and then get a SUM()

I'm trying to do it, eg

SELECT @TOT = SUM(cast(CAST(isnull(decryptbykey(TOT_TRAB_INCLUIDOS),0) as varchar(15)) as float))   from TABLE_XX

or 

 SELECT @TOT_Trabajadores = SUM(cast(CAST(decryptbykey(TOT_TRAB_INCLUIDOS) as varchar(15)) as float))  from TABLE_XX

This error show me: Operand data type varbinary(max) is invalid for sum operator.

Someone may i help me, please :(

Thks so much

Answer 1

>This error show me: Operand data type varbinary(max) is invalid for sum operator.

SUM requires numeric data.  Convert varbinary(max) to numeric first.

---

Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 2

I've tried to do this:

 SUM( cast( CAST( decryptbykey(Tot_Trab_Incluidos) as varchar(15)) as int) ) 

But i get the same error.

So, i have to convert my rows to numeric first in a temporary table, and then try to SUM(), is the only way?

Thanks so much

Answer 3

Demo:

DECLARE @vb varbinary(max) = newid();
-- 0x098D5DFFFDFD32488EEAA020EBDA7F17
SELECT @vb;
SELECT convert(bigint, @vb);   -- -8148524512517128425
SELECT SUM(convert(bigint, @vb));  -- -8148524512517128425

---

Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 4

The error message comes from another piece of code than you are showing is. In this example SUM sees a float value. decryptbykey returs varbinary(8000) and not varbinary(MAX), so this clearly not the culprit.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

My rows are encrypted with Symetrics Keys, in my stored procedure,

1.- Open my simetric key,

2. I have a function descrypted decryptbykey

3. I think that i have to valited if my value is NULL, but i dont know how to do this if my column is varbinary.

4. I've tried to convert first in varchar and then in numeric, so then try to sum()

I dont know if its better to save my information in a temporary table with desencryted data, and then do a SUM with this information.

Answer 6

To help you we would need to see a full script that demonstrates the problem, and the full error message, including line number.

It would also help to see the output from "SELECT @@version".

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

"SELECT @@version".

Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)   Dec 10 2010 10:56:29   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 

Answer 8

ALTER PROCEDURE sp_Carga    
 @LLAVE   varchar(11),    
 @REG_PAT  INT        
AS    
BEGIN    
 DECLARE 
 @TOT      numeric(15,2),
 @Excedente numeric(15,2) 

  OPEN SYMMETRIC KEY EncriptaData_Key         
  DECRYPTION   BY CERTIFICATE [cert_SIASS_Key2013]

   SELECT  
      @TOT = sum(cast(CAST(decryptbykey(imss_cuota_fija) as varchar(15)) as float) )
     ,@Excedente = sum(cast(CAST(decryptbykey(IMSS_Exced_3_SMGDF) as varchar(15)) as float) )  
    from TABLE_1    
    where LLAVE = @LLAVE and id_reg_pat = @REG_PAT    
    GROUP BY LLAVE, id_reg_pat  

  CLOSE SYMMETRIC KEY EncriptaData_Key  
END  

Show me error, in the lines when built SUM()

 

Answer 9

I have had similar problems before.  Try this:

SELECT 
@TOT = SUM(imss_cuota_fija),
@Excedente = sum(IMSS_Exced_3_SMGDF)
FROM (
SELECT  LLAVE, id_reg_pat,
       imss_cuota_fija = (cast(CAST(decryptbykey(imss_cuota_fija) as varchar(15)) as float) )
      ,IMSS_Exced_3_SMGDF = (cast(CAST(decryptbykey(IMSS_Exced_3_SMGDF) as varchar(15)) as float) )  
     from TABLE_1    
     where LLAVE = @LLAVE and id_reg_pat = @REG_PAT    
) a
     GROUP BY LLAVE, id_reg_pat  

Answer 10

Thank you so much!