Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: 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?

    Code:
    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 B

  2. #2
    Join Date
    Feb 2004
    Posts
    134
    I tried the following
    Code:
    SELECT CAST(@dValue, AS VARCHAR) AS "cValue"
    but I get the error "Error 156 : Incorrect syntaxt near keyword 'AS'"

    Mike B

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    I figured it out. I used the CAST

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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Convert from money to varchar?

    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?

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    134

    Re: Convert from money to varchar?

    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!

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well if @@Error = 0 and @@ROWCOUNT = 2, then your statement would be true, no?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Posts
    134
    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
    Code:
    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
    Last edited by MikeB_2k4; 03-08-04 at 16:56.

  8. #8
    Join Date
    Feb 2004
    Posts
    134
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •