Tuesday, February 14, 2012

Convert from money to varchar?

I want to print in an error message a money value but have to convert it to a varchar first. I do not have any clue how to do this. Could someone help me out?

SELECT @.iError = @.@.error, @.iRowCount = @.@.rowcount
IF(@.iError <> 0 OR @.iRowCount <> 1)
BEGIN
CONVERT(@.dValue, @.cValue)
set @.cError = 'Error attempting to insert new record.\n\t\tProduct : ' + @.cProduct + '\n\t\tSub-account : ' + @.cSubAccount + '\n\t\tCost : $' + @.cValue
RAISERROR(@.cError, 16, 1)
END

@.cValue is a varchar(20)
@.dValue is money

Any help?

Mike BI tried the following

SELECT CAST(@.dValue, AS VARCHAR) AS "cValue"

but I get the error "Error 156 : Incorrect syntaxt near keyword 'AS'"

Mike B|||I figured it out. I used the CAST

set @.cError = ............ + '\n\t\tCost : $' + CAST(@.dValue AS VARCHAR)

Mike B|||Originally posted by MikeB_2k4
I want to print in an error message a money value but have to convert it to a varchar first. I do not have any clue how to do this. Could someone help me out?

SELECT @.iError = @.@.error, @.iRowCount = @.@.rowcount
IF(@.iError <> 0 OR @.iRowCount <> 1)
BEGIN

Hey...looks vaguely familiar...

And I think you need an AND instead...

Why not break it up in to 2 clauses though...

That way you'll know it 1 of 2 types of errors...the first a system problem and the second a user error...

It's more coding, but you'll never be in doubt|||Originally posted by Brett Kaiser
Hey...looks vaguely familiar...

hehe, you didn't copyright it I hope? :)

And I think you need an AND instead...

I think OR is required because I am expecting only 1 row to be inserted. Therefore, if more or less then one row?

Why not break it up in to 2 clauses though...

Good Suggestion.

Thanks
Mike B

P.S., I will try to remember to give credit where credit is due next time I copy some of your code! ;)|||Well if @.@.Error = 0 and @.@.ROWCOUNT = 2, then your statement would be true, no?|||Originally posted by Brett Kaiser
Well if @.@.Error = 0 and @.@.ROWCOUNT = 2, then your statement would be true, no?

No, I don't even no why I am checking rowcount. Maybe just to confuse myself. The stored procedure inserts a record and only a single record.

I should really only be checking the error value probably, but due to lack of experience, I still don't trust the insertion, deletion etc.. statements in a SQL statement.

I have a habit of checking to ensure the record count is what is expected, nothing more, nothing less. Unnecessary but.....

Thanks for your input.

Mike B

In case your interested, here is the Stored proc

CREATE PROCEDURE usp_updateProductCost

@.dValue money,
@.cProduct varchar(10),
@.cSubAccount varchar(10)

AS

declare @.cError varchar(255), @.iError int, @.iRowCount int

if(EXISTS(SELECT * FROM tbProductCosting WHERE tbProductCosting.Product = @.cProduct AND tbProductCosting.SubAccount = @.cSubAccount))
BEGIN
Update tbProductCosting SET tbProductCosting.Cost = @.dValue WHERE tbProductCosting.Product = @.cProduct AND tbProductCosting.SubAccount = @.cSubAccount
SELECT @.iError = @.@.error

if(@.iError <> 0)
BEGIN
set @.cError = 'Error update cost for product ' + @.cProduct
RAISERROR(@.cError, 16, 1)
END
END
else
BEGIN
INSERT INTO tbProductCosting (Product, SubAccount, Cost) VALUES (@.cProduct, @.cSubAccount, @.dValue)
BEGIN
SELECT @.iError = @.@.error, @.iRowCount = @.@.rowcount
IF(@.iError <> 0 OR @.iRowCount <> 1)
BEGIN
set @.cError = 'Error attempting to insert new record.\n\t\tProduct : ' + @.cProduct + '\n\t\tSub-account : ' + @.cSubAccount + '\n\t\tCost : $' + CAST(@.dValue AS VARCHAR)
RAISERROR(@.cError, 16, 1)
END
END
END
GO|||I do see one major problem now if I look at it. Even if the record count was greater then 1, I don't handle it. I should be using transaction management. hmmmm

Mike B

No comments:

Post a Comment