Real answer not Integer please.

[RobHVB6Sql]

I have written a stored procedure that should be simple.
The problem is I get an integer answer (0 or 1) when dividing two real variables -I want a two decimal place answer, you can see I have experimented with CAST and data types and I doubt I need as many variables as I have.

I have cheated and set values in the proc below so you can run it. The answer should be 66.6% (1000/1500).
Any help appreciated, thanks. (Using SQL 7.0 /T-SQL)

/*********************************************************/
/* spShowConvert */
/* Creates print statements to show conversion progress. */
/*********************************************************/
if exists (select * from sysobjects where id = object_id('spShowConvert') and sysstat & 0xf = 4)
drop procedure spShowConvert
GO

PRINT 'Creating spShowConvert stored procedure'
GO

CREATE PROCEDURE spShowConvert
(
@FromTable VARCHAR(50)
)
AS

DECLARE @ToTable VARCHAR(50)
DECLARE @NoToConvert AS float(24)
DECLARE @NoConverted AS float(24)
DECLARE @TotalRowsToConvert AS float(24)
DECLARE @PercentageDone AS float(24) --INTEGER

IF @FromTable = 'tProcedureCOPY'
BEGIN
SELECT @NoToConvert = COUNT(ProcID) FROM tProcedureCOPY
-- CHEAT FOR tek-tips below
SELECT @NoToConvert = 5000
SELECT @ToTable = 'tblProcedure'
SELECT @NoConverted = COUNT(ProcedureID) FROM tblProcedure
-- CHEAT FOR tek-tips below
SELECT @NoConverted = 10000
END

/* Show the results */
PRINT ''
PRINT 'Records to convert (' + @FromTable + ') :' + STR(@NoToConvert)
PRINT 'Records converted (' + @ToTable + ') :' + STR(@NoConverted)
SELECT @TotalRowsToConvert = (@NoConverted + @NoToConvert)
SELECT @TotalRowsToConvert = CAST(@TotalRowsToConvert AS float(24))
PRINT '------------------------------------------------------------'
PRINT 'Total Rows To Convert :' + STR(@TotalRowsToConvert)
SELECT @NoConverted = CAST(@NoConverted AS float(24))
--SELECT @PercentageDone = @NoConverted / @TotalRowsToConvert
SELECT @PercentageDone = CAST(@NoConverted AS float(24)) / CAST(@TotalRowsToConvert AS float(24))
SELECT @PercentageDone = ROUND(@PercentageDone,4)
PRINT 'Percentage Done :' + STR(@PercentageDone) + '%'
GO

spShowConvert 'tProcedureCOPY'


Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[nigelrivett]

Shouldn't it be 66.67?
to get 66.6% from (1000/1500).

select convert(decimal(8,2),100.0*1000/1500)
100 * 1000/1500 would be calculated in integer arithmetic and hence lose any decimal places.
The 100.0 forces it to retain the precision.
You can get the same effect by
select convert(decimal(8,2),convert(numeric(18,4),100*1000)/1500)



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[RobHVB6Sql]

Thanks Nigel, that is very helpful. The answer comes out with the decimal places now.

The next problem is showing the result with the PRINT statement. I need to convert the result to a string and the DP's are lost again.

Code I'm using:
SELECT @PercentageDone = Convert(decimal(8,2),
Convert(numeric(18,4),100) *
@NoConverted ) /
@TotalRowsToConvert)

PRINT 'Percentage Done :' + STR(@PercentageDone) + '%'
GO


Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[nigelrivett]

Have a look at the str function in bol.

PRINT 'Percentage Done :' + STR(@PercentageDone,20,2) + '%'

you might want
PRINT 'Percentage Done :' + convert(varchar(20),(@PercentageDone,20,2) + '%'



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[RobHVB6Sql]

Yep, all working now, thank you.

Final version =
PRINT 'Percentage Done :' +
Convert(varchar,@PercentageDone) + '%'

Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)