Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT

Question

I am getting the following error:

"Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query."

Note:

@transientCartData is defined as varbinary(max)

@savedCartData is defined as varbinary(max)

The query:

exec [ecomm].[expiry_save_cart2_v1] 
@id=80094, 
@lastUpdated='2016-05-11 14:23:42.637',
@transientCartExpiryIntervalInMin='45',
@session_id='5C632166-D7D6-4F51-A87F-EED41376EEA7',
@version='TEST_VERSION',
@associated_slots='Null',
@account_id='TEST_ACCOUNT_ID',
@cookie='Null',
@transientCartData= 'TEST_DATA',
@savedCartData= 'TEST_DATA',
@data2='NULL',
@is_pricing_needed='1',
@savedCartExpiryIntervalInDays='14',
@persistentCartExpiryIntervalInDays='1',
@cart_type='PERSISTENT', 
@customerAccountId=TEST_ACCOUNT_ID

Then when I am trying to use @transientCartData = CONVERT(varbinary, 'TEST_DATA', 1), I am getting this error:

Incorrect syntax near the keyword 'CONVERT'.

Answer 1

SQL-Server

SQL-Server has the following syntax for Convert.

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

You code is failing due to style being set as 1.

If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). ...

You need to set it to 0(default).

Translates ASCII characters to binary bytes or binary bytes to ASCII characters...

Example convert usage, which produces 0x544553545F44415441

Declare @vb as varbinary(max)
Set @vb =  CONVERT(varbinary(max), 'TEST_DATA', 0)
select @vb

---

MySQL

According to the documentation for Convert your method signature is incorrect.

CONVERT has the following MySQL syntax:

CONVERT(expr,type), CONVERT(expr USING transcoding_name)

---

Calling a stored procedure uses Call in MySQL.

CALL sp_name([parameter[,...]])

An example of the usage is:

mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+--------------+------------+
| @version     | @increment |
+--------------+------------+
| 5.5.3-m3-log |         11 |
+--------------+------------+