Interpreting byte in stored procedure

A proc we have searches an encrypted field by encrypting the search field and comparing these encrypted values. What I need though to be able to do is to pass into the proc (through Entity Framework 4) the encrypted value (as the code encrypts it), but also allow null if the value is not provided.

So I need to pass in a byte[] but it also needs to accept nulls… is this even possible, or what is a workaround if its not? Again, I’m calling a stored procedure through entity framework.

  • Column does not allow nulls
  • SQL update query using joins
  • Explain group by query
  • SQL Server to MYSQL Migration tool
  • SQL Server: halt an INSERT in a trigger
  • trim left characters in sql server?
  • Thanks.

  • How does fetching data from SQL Server to SqlDataReader work?
  • Prepared statements and the built-in connection pool in .NET
  • SQL Server database insert/update TCP notifications
  • SQL Server table isolation level and lock issue
  • How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer?
  • Randomly slow page loading
  • 2 Solutions collect form web for “Interpreting byte in stored procedure”

    Given this stored procedure:

    create procedure dbo.pConvertBytesToInt
    
      @bytes varbinary(4)
    
    as
    
      select convert(int,@bytes)
    
    go
    

    The following code will execute it, passing NULL if the parameter passed is null:

    static int? Bytes2IntViaSQL( byte[] @bytes )
    {
      int? value ;
      const string connectionString = "Data Source=localhost;Initial Catalog=sandbox;Integrated Security=SSPI;" ;
      using ( SqlConnection connection = new SqlConnection( connectionString ) )
      using ( SqlCommand    sql        = connection.CreateCommand() )
      {
        sql.CommandType = CommandType.StoredProcedure ;
        sql.CommandText = "dbo.pConvertBytesToInt" ;
    
        SqlParameter p1 = new SqlParameter( "@bytes" , SqlDbType.VarBinary ) ;
        if ( @bytes == null ) { p1.Value = System.DBNull.Value ; }
        else                  { p1.Value = @bytes              ; }
    
        sql.Parameters.Add( p1 ) ;
    
        connection.Open() ;
        object result = sql.ExecuteScalar() ;
        value = result is DBNull ? (int?)null : (int?)result ;
        connection.Close() ;
    
      }
    
      return value ;
    }
    

    This test harness

    static void Main( string[] args )
    {
      byte[][] testcases = { new byte[]{0x00,0x00,0x00,0x01,} ,
                             null                   ,
                             new byte[]{0x7F,0xFF,0xFF,0xFF,} ,
                           } ;
    
      foreach ( byte[] bytes in testcases )
      {
          int? x =  Bytes2IntViaSQL( bytes ) ;
          if ( x.HasValue ) Console.WriteLine( "X is {0}" , x ) ; 
          else              Console.WriteLine( "X is NULL" ) ;
      }
    
      return ;
    }
    

    produces the expected results:

    X is 1
    X is NULL
    X is 2147483647
    

    We ended up getting it to work by pushing it as a string, and then parsing it in the proc. That worked. But I believe I read there is a Binary object that represents the byte[] array, and that would have worked too.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.