sum result type character

Question

sum result type character

elkinfortiz

Mr or Mrs. 500

Points: 533
More actions
February 1, 2015 at 10:00 am

#314897

i neek result from '(1000/3)+(15/100)'. try exec() by failed. Thands for your help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

elkinfortiz (2/1/2015)
i neek result from '(1000/3)+(15/100)'. try exec() by failed. Thands for your help.

Not sure why you would try EXEC() for this. You just need a SELECT with the understanding that you're fighting integer math. In integer math, 15/100 is 0 (with a 15 remainder that won't show) and 1000/3 will give you 333 (with a 1 remainder that won't show). The following will give you what I think you want.

SELECT (1000/3.0)+(15/100.0);

The reason why that works it because neither 3.0 nor 100.0 are integers and they'll cause the formulas to use something other than integer math.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

elkinfortiz Mr or Mrs. 500 Points: 533 More actions · Answer 2

If your analysis is correct. But this strip of operations corresponds to an interpretation of fields, sort of mini compiler, which translates : ' (WIDTH / 3 ) + ( LONG / 100) ' . The process interprets the width and length in numbers and should run the operation to obtain the final result. '(100/3)+(15/100)'. thanks for your help.

ScottPletcher SSC Guru Points: 101097 More actions · Answer 3

You can use sp_executesql, something like this:

DECLARE @WIDTH decimal(19, 2)

DECLARE @LONG decimal(19, 2)

DECLARE @RESULT decimal(19, 2)

DECLARE @sql nvarchar(4000)

SET @sql = N'( @WIDTH / 3 ) + ( @LONG / 100)'

SET @sql = 'SELECT @RESULT = ' + REPLACE(REPLACE(@sql, '@WIDTH', '100'),'@LONG', '15')

PRINT @sql

EXEC sp_executesql @sql, N'@RESULT decimal(19, 2) OUTPUT, @width decimal(19, 2), @long decimal(19, 2)',

@RESULT OUTPUT, @WIDTH, @LONG

SELECT @result

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

elkinfortiz (2/2/2015)
If your analysis is correct. But this strip of operations corresponds to an interpretation of fields, sort of mini compiler, which translates : ' (WIDTH / 3 ) + ( LONG / 100) ' . The process interprets the width and length in numbers and should run the operation to obtain the final result. '(100/3)+(15/100)'. thanks for your help.

There is no need for dynamic SQL here. Please post the query you have so far.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

elkinfortiz Mr or Mrs. 500 Points: 533 More actions · Answer 5

Thank you very much everyone for your responses. I resolved my problem by way of sq._exec (). Thank you all .