[SOLVED] [SQL Server]Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query

[SQL Server]Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query

That is my code

DB::statement(DB::raw('EXECUTE dbo.SP_WS_CUST_MAIN ?,?,?,?,?,?,?'),
    [ $USER_ID, $CUST_PYMT_MTHD, $CUST_CMMNT, $CUST_NAME, $ANDROID_LOCATION,
      DB::raw("CONVERT(VARBINARY(MAX), $value)") , $WEEKDAY]
);

How can convert nvarchar(max) to varbinary(max)?

Solution

I can think of three possibilities. The simplest would be to create a shell stored procedure (say dbo.SP_WS_CUST_MAIN_VC) that simply accepts the parameters, including $value as a varchar(max), then does the conversion of $value to a varbinary(max) and calls the target stored procedure. This ensures proper quoting with the parameter replacement.

The second (and most hazardous) is to embed the CONVERT into the parameters directly, opening yourself to all sorts of SQL Injection ugliness.

The third would be to create a UDF that accepts a varchar(max) and returns a varbinary(max), and embed that into the base EXECUTE call, e.g.:

DB::statement(DB::raw('EXECUTE dbo.SP_WS_CUST_MAIN ?,?,?,?,?,dbo.castAsVarbinary(?),?'),
    [ $USER_ID, $CUST_PYMT_MTHD, $CUST_CMMNT, $CUST_NAME, $ANDROID_LOCATION,
      DB::raw("CONVERT(VARBINARY(MAX), $value)") , $WEEKDAY]
);

Any of these would work, but (1) and (3) should also be secure.