Stored procedure decimal precision

filipczako · November 28th, 2005, 06:39 AM

i have very simple SP:

Code:

CREATE PROC [dbo].[mySP]  
AS SELECT  ( DecimalValue1 * DecimalValue1 ) AS [COLUMN 1],  
( DecimalValue1 + DecimalValue1 ) AS [COLUMN 2] FROM mytab;

1)all DecimalValues are decimal(18,2) in DB table and i want to have a result with that precision
2)i want to "use" results from previous colums in next columns.
how to do it ?
3)how to reorder columns in dataset (by c# code)?

greeting from Poland
thx in A.
Filip

jbenson001 · November 28th, 2005, 02:21 PM

CREATE PROC [dbo].[mySP]
AS SELECT Convert(decimal(18,2),( DecimalValue1 * DecimalValue1 )) AS [COLUMN 1],
Convert(decimal(18,2), ( DecimalValue1 + DecimalValue1 )) AS [COLUMN 2] FROM mytab;

filipczako · November 28th, 2005, 02:28 PM

Quote:

quote:Originally posted by jbenson001
CREATE PROC [dbo].[mySP]
AS SELECT Convert(decimal(18,2),( DecimalValue1 * DecimalValue1 )) AS [COLUMN 1],
Convert(decimal(18,2), ( DecimalValue1 + DecimalValue1 )) AS [COLUMN 2] FROM mytab;

convert so easy...and i did not know it:(
thanks

jbenson001 · November 28th, 2005, 02:34 PM

Glad to help :)

Jim

filipczako · November 28th, 2005, 03:24 PM

Quote:

quote:Originally posted by jbenson001
Glad to help :)

Jim

if u knew answer for 2) question...:)

jbenson001 · November 28th, 2005, 03:59 PM

Can you give an example of what you are trying to do?

filipczako · November 28th, 2005, 04:14 PM

Quote:

quote:Originally posted by jbenson001
Can you give an example of what you are trying to do?

i want to have column1 results in column2...
Example (doesn't work of course:):
CREATE PROC [dbo].[mySP]
AS SELECT ( DecimalValue1 * DecimalValue1 ) AS [COLUMN 1],
( DecimalValue1 + DecimalValue1*COLUMN1) AS [COLUMN 2] FROM mytab;

Column 2 use column1 results.

jbenson001 · November 28th, 2005, 04:27 PM

You can do excactly what you have written except that you cannot use column1, you need to use the actual column name.

filipczako · November 28th, 2005, 04:43 PM

Quote:

quote:Originally posted by jbenson001
You can do excactly what you have written except that you cannot use column1, you need to use the actual column name.

so i should rewrite the same instructions...
i thought that it could be slower solution.

jbenson001 · November 28th, 2005, 04:49 PM

Either use:
DecimalValue1 + DecimalValue1*(DecimalVal1*DecimalVal2) as col2

OR
Use a temp table
SELECT ( DecimalValue1 * DecimalValue1 ) AS [COLUMN 1], DecimalValue1, DecimalValue1
into #temp

select [column 1], DecimalValue1 + DecimalValue1 * [column 1] AS [column 2]
From #Temp