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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment