SQL Query yntax - SQL Server 2005 - Convert Float Sum

Hello experts,

I have a script below, that largely functions the way that I want it to.  This Stored Procedure basically takes two fields passed in from a program, adds them together, and outputs them.  The values passed in are positive and negative numbers from text fields, and the output must be a text field as well, which is why you see the convert functions in the set statement.

The problem - is that when I have to add whole numbers like '1.00' and '2.00' it returns the value of '3' instead of '3.00'.  Is there a way for me to add a case statement or something that will append '.00' anytime there is no decimal for example?  There has to be an easier way to do this.

Thanks for the help!
ALTER procedure [dbo].[data_OD]
  @enc_id varchar(36),
  @txt_field_1 varchar(70),
  @txt_field_2 varchar(70),
  @txt_field_sum VARCHAR(50) output
as

select @txt_field_1 = receive_data_.odsph_gl1
from receive_data_, patient_encounter
where receive_data_.enc_id = patient_encounter.enc_id
and patient_encounter.enc_id = @enc_id

select @txt_field_2 = receive_data_.odcyl_gl1
from receive_data_, patient_encounter
where receive_data_.enc_id = patient_encounter.enc_id
and patient_encounter.enc_id = @enc_id
 
SET @txt_field_sum = CONVERT(VARCHAR(50),convert(FLOAT,@txt_field_1) +
    convert(FLOAT,@txt_field_2))

Open in new window

robthomas09Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
Try this:

SET @txt_field_sum = CONVERT(VARCHAR(50),convert(money,@txt_field_1) +  convert(money,@txt_field_2), 0)

When using CONVERT, the Money datatype has a conversion option that returns two decimal places.
robthomas09Author Commented:
Thanks bhess!  That got me decimal places perfectly. Is there a way to prefix it with a + sign for a pos number and a minus sign for a neg number?

Thanks!
Brendt HessSenior DBACommented:
Try this:

SET @txt_field_sum = CASE Sign(convert(money,@txt_field_1) +  convert(money,@txt_field_2))
    WHEN 1 THEN '+'
    WHEN -1 THEN '-'
    ELSE '' END + CONVERT(VARCHAR(50),convert(money,@txt_field_1) +  convert(money,@txt_field_2), 0)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
try this

ALTER procedure [dbo].[data_OD]
  @enc_id varchar(36),
  @txt_field_1 varchar(70),
  @txt_field_2 varchar(70),
  @txt_field_sum VARCHAR(50) output
as

select @txt_field_sum  =convert(money, convert(float,receive_data_.odsph_gl1)
   +convert(float,receive_data_.odcyl_gl1),2)  
from receive_data_, patient_encounter
where receive_data_.enc_id = patient_encounter.enc_id
and patient_encounter.enc_id = @enc_id

return
go

Open in new window

robthomas09Author Commented:
Perfect
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.