March 31, 2011 at 10:16 am
In below query, I have written a select query with a parameter, the parameter value "@WSUrnam" is the column in the database table which is of type varchar but holds all numeric values and I want to cast this to decimal but i am not able to do that, any help wil be appreciated.
Thanks!!
declare @WSUrid numeric
declare @WSUrnam varchar (100)
declare @WSPrice1 varchar (100)
declare @WSPrice2 numeric
declare @WholesalePrice decimal (10,2) = 200
declare @Orderid numeric = 348109
declare @Productid numeric = 59019
set @WSUrid = (Select wholesaleuserid from dbo.Orders_tempTb where orderid=@OrderID)
if(@WSUrid <> 0 )
begin
set @WSUrnam = (Select Login from dbo.WholesaleUsersTb where Wholesaleusersid = @WSUrid)
set @WSPrice1 = 'Select ISNULL(' + @WSUrnam + ',0) from dbo.productstb where productid = ' + @ProductID
set @WSPrice2 = CONVERT(int,@WSPrice1) + @WholesalePrice
Update dbo.OrderDetails_tempTb set sale_price = @WSPrice1 where productID = @productID and OrderID = @OrderID
end
March 31, 2011 at 10:27 am
Are you getting any error? Also there is no code like sp_executesql or EXECUTE command to get the data from database to @WSPrice1 variable.
Regards,
Shaiju CK
_____________________________________________
One ounce of practice is more important than tonnes of dreams
March 31, 2011 at 10:30 am
I AM GETTING FOLLOWING BELOW ERROR
Msg 8115, Level 16, State 6, Line 14
Arithmetic overflow error converting varchar to data type numeric.
And also correction in above update statement it is
Update dbo.OrderDetails_tempTb set sale_price = @WSPrice2 where productID = @productID and OrderID = @OrderID
March 31, 2011 at 12:34 pm
Try this:
declare @WSUrid numeric
declare @WSUrnam varchar (100)
declare @WSPrice1 varchar (100)
declare @WSPrice2 numeric
declare @WholesalePrice decimal (10,2) = 200
declare @Orderid numeric = 348109
declare @Productid numeric = 59019
Select @WSUrid = wholesaleuserid from dbo.Orders_tempTb where orderid=@OrderID
if(@WSUrid <> 0 )
begin
Select @WSUrnam = Login from dbo.WholesaleUsersTb where Wholesaleusersid = @WSUrid
Select @WSPrice1 = ISNULL( @WSUrnam ,0) from dbo.productstb where productid = @ProductID
set @WSPrice2 = CONVERT(numeric,@WSPrice1) + @WholesalePrice
Update dbo.OrderDetails_tempTb set sale_price = @WSPrice1 where productID = @productID and OrderID = @OrderID
end
March 31, 2011 at 12:35 pm
declare @WSUrid numeric
Why is this ID field a numeric? You have decimal IDs?
set @WSPrice1 = 'Select ISNULL(' + @WSUrnam + ',0) from dbo.productstb where productid = ' + @ProductID
Do you have different prices for each user? If so, don't make the users a column, make a table of user prices.
Also, this select is not being executed. You are assigning the string to the variable. If you redesign the table, you won't have to try dynamic sql.
But your error is coming in at the end. Wrap it like this "cast(ProductID as varchar(10))"
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
March 31, 2011 at 3:31 pm
CELKO (3/31/2011)
Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.Your local variables make no sense. Numeric is the same as DECIMAL in T-SQL, but we have no scale or precision. Why are you building a query string at all? You got the table names wrong, too.
Was there a point to this or did you just need to sit in your rocker on the porch of the ANSI house with your shotgun yelling at the kids again to keep your heart rate up before you mold?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 31, 2011 at 3:38 pm
ptlbiren (3/31/2011)
I AM GETTING FOLLOWING BELOW ERRORMsg 8115, Level 16, State 6, Line 14
Arithmetic overflow error converting varchar to data type numeric.
And also correction in above update statement it is
Ptl, the problem is probably stemming from the lack of explicit declares on your numeric statement. While I pick on Celko above for being obnoxious, buried in the middle of that is his point. The default for NUMERIC is NUMERIC (18,0). See here: http://msdn.microsoft.com/en-us/library/ms187746.aspx
You'll notice they're equivalent, Numeric and Decimal that is. Numeric isn't like FLOAT, it's a precision declaration. The error above states that whatever value is hiding in your varchar field is too large for an 18 character number with no decimal. Now that's QUITE large, so my guess is you have some kind of exponential value hiding in there (IE: 1.2E20).
Explicitly declare your numerics to a larger size (say, 30,8) and see if the error continues. If it does, you're going to have to go into the table/column and attempt to locate the offending pieces of data.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 31, 2011 at 7:50 pm
ptlbiren (3/31/2011)
I AM GETTING FOLLOWING BELOW ERRORMsg 8115, Level 16, State 6, Line 14
Arithmetic overflow error converting varchar to data type numeric.
And also correction in above update statement it is
Update dbo.OrderDetails_tempTb set sale_price = @WSPrice2 where productID = @productID and OrderID = @OrderID
Your error is because of these lines of code:
set @WSPrice1 = 'Select ISNULL(' + @WSUrnam + ',0) from dbo.productstb where productid = ' + @ProductID
set @WSPrice2 = CONVERT(int,@WSPrice1) + @WholesalePrice
@WSPrice1 is a varchar(100) variable, filled with a select statement. You are trying to convert it to an integer.
You need to do something like:
declare @WSPrice1 nvarchar(100); -- needs to be a NVARCHAR
set @WSPrice1 = N'Select @MyVar = ISNULL(' + @WSUrnam + ',0) from dbo.productstb where productid = ' + @ProductID
execute sp_executesql @WSPrice1, N'@MyVar int OUTPUT', @WSPrice2 OUTPUT;
set @WSPrice2 = @WSPrice2 + @WholesalePrice;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 31, 2011 at 8:12 pm
WayneS (3/31/2011)
ptlbiren (3/31/2011)
I AM GETTING FOLLOWING BELOW ERRORMsg 8115, Level 16, State 6, Line 14
Arithmetic overflow error converting varchar to data type numeric.
And also correction in above update statement it is
Update dbo.OrderDetails_tempTb set sale_price = @WSPrice2 where productID = @productID and OrderID = @OrderID
Your error is because of these lines of code:
set @WSPrice1 = 'Select ISNULL(' + @WSUrnam + ',0) from dbo.productstb where productid = ' + @ProductIDset @WSPrice2 = CONVERT(int,@WSPrice1) + @WholesalePrice
@WSPrice1 is a varchar(100) variable, filled with a select statement. You are trying to convert it to an integer.
You need to do something like:
declare @WSPrice1 nvarchar(100); -- needs to be a NVARCHARset @WSPrice1 = N'Select @MyVar = ISNULL(' + @WSUrnam + ',0) from dbo.productstb where productid = ' + @ProductID
execute sp_executesql @WSPrice1, N'@MyVar int OUTPUT', @WSPrice2 OUTPUT;
set @WSPrice2 = @WSPrice2 + @WholesalePrice;
sorry my mistake @WSPrice1 is numeric not varchar, I am still getting error with this conversion.
March 31, 2011 at 8:13 pm
toddasd (3/31/2011)
declare @WSUrid numericWhy is this ID field a numeric? You have decimal IDs?
set @WSPrice1 = 'Select ISNULL(' + @WSUrnam + ',0) from dbo.productstb where productid = ' + @ProductIDDo you have different prices for each user? If so, don't make the users a column, make a table of user prices.
Also, this select is not being executed. You are assigning the string to the variable. If you redesign the table, you won't have to try dynamic sql.
But your error is coming in at the end. Wrap it like this "cast(ProductID as varchar(10))"
yes i have different prices for users and each user is column
March 31, 2011 at 8:15 pm
ColdCoffee (3/31/2011)
Try this:
declare @WSUrid numericdeclare @WSUrnam varchar (100)
declare @WSPrice1 varchar (100)
declare @WSPrice2 numeric
declare @WholesalePrice decimal (10,2) = 200
declare @Orderid numeric = 348109
declare @Productid numeric = 59019
Select @WSUrid = wholesaleuserid from dbo.Orders_tempTb where orderid=@OrderID
if(@WSUrid <> 0 )
begin
Select @WSUrnam = Login from dbo.WholesaleUsersTb where Wholesaleusersid = @WSUrid
Select @WSPrice1 = ISNULL( @WSUrnam ,0) from dbo.productstb where productid = @ProductID
set @WSPrice2 = CONVERT(numeric,@WSPrice1) + @WholesalePrice
Update dbo.OrderDetails_tempTb set sale_price = @WSPrice1 where productID = @productID and OrderID = @OrderID
end
i am getting following below error
Error converting data type varchar to numeric.
Also in above procedure @WSPrice1 is numeric not varchar, it was my mistake earlier that i declared it as numeric.
March 31, 2011 at 9:54 pm
ptlbiren (3/31/2011)
WayneS (3/31/2011)
ptlbiren (3/31/2011)
I AM GETTING FOLLOWING BELOW ERRORMsg 8115, Level 16, State 6, Line 14
Arithmetic overflow error converting varchar to data type numeric.
And also correction in above update statement it is
Update dbo.OrderDetails_tempTb set sale_price = @WSPrice2 where productID = @productID and OrderID = @OrderID
Your error is because of these lines of code:
set @WSPrice1 = 'Select ISNULL(' + @WSUrnam + ',0) from dbo.productstb where productid = ' + @ProductIDset @WSPrice2 = CONVERT(int,@WSPrice1) + @WholesalePrice
@WSPrice1 is a varchar(100) variable, filled with a select statement. You are trying to convert it to an integer.
You need to do something like:
declare @WSPrice1 nvarchar(100); -- needs to be a NVARCHARset @WSPrice1 = N'Select @MyVar = ISNULL(' + @WSUrnam + ',0) from dbo.productstb where productid = ' + @ProductID
execute sp_executesql @WSPrice1, N'@MyVar int OUTPUT', @WSPrice2 OUTPUT;
set @WSPrice2 = @WSPrice2 + @WholesalePrice;
sorry my mistake @WSPrice1 is numeric not varchar, I am still getting error with this conversion.
And it still makes sense... you're trying to put a character string into a numeric.
Hmm - also, your @ProductID would also try to convert that string into a numeric - you need + Convert(varchar(11), @ProductID) there instead
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply