SQL Where with Binary(n) column

Question

I have a stored procedure:

ALTER PROCEDURE [dbo].[spUpdateOrInsertNotification]
    @ContentJsonHash BINARY(32)
AS
    DECLARE @NotificationId INT;

    SET @NotificationId = (SELECT @NotificationId
                           FROM dbo.tblNotifications n
                           WHERE n.ContentJsonHash = @ContentJsonHash);

    IF @NotificationId IS NOT NULL  
    BEGIN
        -- Increment Count
    END
    ELSE
    BEGIN
         -- Insert new row.
    END

It's supposed to check if the Hash already exists and if it does, increment the count for the row, otherwise insert the row. However, it never finds the Hash and the corresponding NotificationId. NotificationId is always null.

If I run it twice, passing it the same data (a C# array byte[32]). It never finds the same NotificationId and I end up with duplicate entries being put in.

e.g.

NotificationId | ContentJsonHash
9                0xB966C33517993003D789EDF78DA20C4C491617F8F42F76F48E572ACF8EDFAC2A
10               0xB966C33517993003D789EDF78DA20C4C491617F8F42F76F48E572ACF8EDFAC2A

Can I not do comparisons on Binary(n) fields like this WHERE n.ContentJsonHash = @ContentJsonhash ?

The C# code:

using (var conn = new SqlConnection(Sql.ConnectionString))
{
     await conn.OpenAsync();

     using (var cmd = new SqlCommand(Sql.SqlUpdateOrInsertNotification, conn))
     {
          cmd.CommandType = CommandType.StoredProcedure;

          cmd.Parameters.AddWithValue("@Source", notificationMessage.Source);
          cmd.Parameters.AddWithValue("@Sender", notificationMessage.Sender);
          cmd.Parameters.AddWithValue("@NotificationType", notificationMessage.NotificationType);
          cmd.Parameters.AddWithValue("@ReceivedTimestamp", notificationMessage.Timestamp);
          cmd.Parameters.AddWithValue("@ContentJSon", notificationMessage.NotificationContent);
          cmd.Parameters.AddWithValue("@ContentJsonHash", notificationMessage.ContentHashBytes);

          await cmd.ExecuteNonQueryAsync();
    }
}

I've also tried calling the stored procedure from SQL like this:

exec dbo.spUpdateOrInsertNotification 'foo', 'bar',  0, 
                                      '2017-12-05 15:23:41.207', '{}',
                              0xB966C33517993003D789EDF78DA20C4C491617F8F42F76F48E572ACF8EDFAC2A

Calling this twice returns 2 rows :(

I can do this, which works, hard coding the binary field I want to check

select *
from dbo.tblNotifications
where ContentJsonhash = 0xB966C33517993003D789EDF78DA20C4C491617F8F42F76F48E572ACF8EDFAC2A

That should be perfectly fine. What exact type is the column and how do you call the sp from C#? — Alex K.
– Alex K., Commented Dec 5, 2017 at 16:37
(Also bear in mind that a row could be inserted in-between you reading into @NotificationId and then using it) — Alex K.
– Alex K., Commented Dec 5, 2017 at 16:39

Matt Spinks · Accepted Answer · 2017-12-06 15:26:12Z

1

Binary comparisons can be tricky. If you are using a true binary column, I believe length also comes into play. So even if those bytes are the same, and the lengths differ, the comparison would be false. An easy way is to convert these to strings:

alter procedure [dbo].[spUpdateOrInsertNotification]
    @ContentJsonHash BINARY(32)
AS

DECLARE @NotificationId INT;
SET @NotificationId = (SELECT NotificationId
                       FROM dbo.tblNotifications n
                       WHERE convert(varchar(32), n.ContentJsonHash, 2) = convert(varchar(32), @ContentJsonHash, 2));

IF @NotificationId IS NOT NULL  
BEGIN
   -- Increment Count
END
ELSE
BEGIN
   -- Insert new row.
END

edited Dec 6, 2017 at 15:26

answered Dec 5, 2017 at 16:48

Matt Spinks

6,7404 gold badges32 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stuart · Accepted Answer · 2017-12-06 08:20:38Z

0

I had an @ where I shouldn't have had an ampersand.

SET @NotificationId = (SELECT @NotificationId
                       FROM dbo.tblNotifications n
                       WHERE convert(varchar(32), n.ContentJsonHash, 2) = convert(varchar(32), @ContentJsonHash, 2));

Should be

SET @NotificationId = (SELECT NotificationId
                       FROM dbo.tblNotifications n
                       WHERE convert(varchar(32), n.ContentJsonHash, 2) = convert(varchar(32), @ContentJsonHash, 2));

I feel so stupid for not noticing this sooner :(

answered Dec 6, 2017 at 8:20

Stuart

4,3187 gold badges33 silver badges63 bronze badges

Collectives™ on Stack Overflow

SQL Where with Binary(n) column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related