convert 'null' varchar to decimal

I have a requirement to create some xml structs (to borrow a C-phrase) in sql-server-2005. In order to do this, I change all my values to varchar. The problem arises when I want to make USE of these values, i have to convert them to decimal.

So, my xml code looks like this:

set @result = @result + <VAL>' + coalesce(cast(@val as varchar(20)), '-.11111') + '</VAL>'

this way, if VAL is null, I return a special decimal and I can check for that decimal. The drawback of doing this, is that I can't use coalesce on the other end when I use 开发者_开发技巧the value, I have to check if it converted value is equal to 0. like this:

case when cast(InvestmentReturn.fn_getSTRUCT(...args...).value('results[1]/VAL[1]', 'varchar(40)')as decimal(10,5)) = -.11111

Since performance is unacceptable right now, I thought one way to improve performance might be to use coalesce instead of using a nested case statement and checking the value for equality with my special 'null' equivalent.

Any thoughts?

also, i see that select cast('null' as decimal(10,5)) gives me:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

---

Performance issues can be caused by a number of factors.

The first one is using XML in sql 2005. I don't know the size of the xml data you are using but when I tried this 5 years ago if you crossed a certain size barrier (I think it was 32k, might have been 64k) then processing performance fell off the cliff. 1 extra byte would cause a query to go from 500ms to 60 seconds. We had to abandon letting SQL server deal with XML data itself at that point. It was MUCH faster to do that processing in C#.

The second one is making calls to functions inside a select statement. If that function has to operate on multiple rows, then performance goes down. One example I always use to illustrate this is GETDATE(). If you set a variable to the return of GETDATE() and then use that variable in a select query it will run an order of magnitude faster than calling GETDATE() in the query itself. The little code example you provided could be a killer just because it's calling a function.

This may not be a good answer to your immediate problem, but I really believe you would be much better served yanking any XML processing code out of SQL server and doing it in ANY OTHER language of your choice.