How do you convert VARCHAR to TIMESTAMP in MSSQL?

Since timestamp is compatible with varbinary the solution will be this in SQL Server 2008:

declare @hexstring varchar(max);
set @hexstring = '0xabcedf012439';
select CONVERT(varbinary(max), @hexstring, 1);

set @hexstring = 'abcedf012439';
select CONVERT(varbinary(max), @hexstring, 2);

Reference. MSN Blogs

A TIMESTAMP is semantically equivalent to VARBINARY(8) (nullable) or BINARY(8) (non-nullable). So you should be able to call the procedure with the parameter unquoted, as follows:

EXEC usp_MyProc @myParam=0x0000000002C490C8

See also SQL Books Online

EDIT for updated question ...

I just tried a few experiments. Frankly, I'm curious as to how you got this represented as a varchar in the first place, since when I do something like:

select top 10 convert(varchar, ts) from foo

Where ts is a timestamp, I get 10 blank rows. (If I don't convert, I see my timestamps.)

However, I tried working at it from the proper direction ... I did this:

select convert(timestamp, '0x0000000000170B2E')

And the conversion resulted in 0x3078303030303030. So that won't play either. Nor will converting to binary.

I hate to say it, but you might be stuck in a dynamic SQL world. I'd really like to be wrong, though.