decimal return on stored procedure.

There's something thats driving me nuts. I have a stored procedure that
returns the result of dividing two different stored prodecures. Here's the
code in QA:
declare @.trhhp decimal(9, 2)
declare @.acp decimal(9, 2)
exec @.trhhp = [clas0_TeleFlash].[dbo].[FIRST_SP]]
exec @.acp = [clas0_TeleFlash].[dbo].[SECOND_SP]
select @.acp / @.trhhp
.003551038825
(1 row(s) affected)
...now when I place it into a stored procedure:
CREATE PROCEDURE [dbo].[PERCERNT_MKT_PENETRATION] AS
declare @.trhhp decimal
declare @.acp decimal
exec @.trhhp = [clas0_TeleFlash].[dbo].[FIRST_SP]
exec @.acp = [clas0_TeleFlash].[dbo].[SECOND_SP]
return (@.acp / @.trhhp)
GO
...and then execute the stored procedure, I seems to round the value to
zero. Any idea why this might be? I'm using decimal values when I can...
DECLARE @.RC decimal
-- Set parameter values
EXEC @.RC = [clas0_TeleFlash].[dbo].[PERCERNT_TELE_PENETRATION]
select @.rc
0
(1 row(s) affected)
Thanks for any help!
-- CaseyHow would I set up the output paramater?
I tried changing the sp...
CREATE PROCEDURE [dbo].[PERCERNT_TELE_PENETRATION]
@.result decimal OUTPUT
AS
declare @.trhhp decimal
declare @.acp decimal
exec @.trhhp = [clas0_TeleFlash].[dbo].[TELE_READY_HHP]
exec @.acp = [clas0_TeleFlash].[dbo].[ACTIVE_CUSTOMERS_PRI]
SET @.result = (select @.acp / @.trhhp)
return
GO
...and then tried retriving the value in QA:
DECLARE @.result decimal
-- Set parameter values
EXEC [clas0_TeleFlash].[dbo].[PERCERNT_TELE_PENETRATION] @.result output
select @.result
... still returns 0... I imagine I'm doing something wrong...
"Nigel Rivett" wrote:
> A return value is always an integer - you need to use an output parameter
(or
> result set).
> "Casey" wrote:
>|||ah! That would be the problem then. How to I set an output pramater? I tried
recreating the SP:
CREATE PROCEDURE [dbo].[PERCERNT_TELE_PENETRATION]
@.result decimal OUTPUT
AS
declare @.trhhp decimal
declare @.acp decimal
exec @.trhhp = [clas0_TeleFlash].[dbo].[TELE_READY_HHP]
exec @.acp = [clas0_TeleFlash].[dbo].[ACTIVE_CUSTOMERS_PRI]
SET @.result = (select @.acp / @.trhhp)
return
GO
Then, when I try to grab that value out of the sp, I still only get zero...
DECLARE @.result decimal
-- Set parameter values
EXEC [clas0_TeleFlash].[dbo].[PERCERNT_TELE_PENETRATION] @.result output
select @.result
I'm doing something wrong...
"KH" wrote:
> The return value of an SP is a success/failure indicator. As Nigel said us
e a
> recordset or output param.
>
> "Casey" wrote:
>|||> @.result decimal OUTPUT
Try setting scale / precision for your decimal parameter.|||I thought's that's what I did...
"Aaron Bertrand [SQL Server MVP]" wrote:

> Try setting scale / precision for your decimal parameter.
>
>|||Be careful with the decimal data type. Scale is 0 by default => specifying
decimal without precision and scale is equal to decimal(18, 0). And that's
not equal to decimal(9, 2) as you declared it in one of the cases.
ML|||>I thought's that's what I did...
Maybe you're not sure what precision/scale are?
You have:
@.result decimal OUTPUT
AS
declare @.trhhp decimal
declare @.acp decimal
You should try
@.result DECIMAL(9,2) OUTPUT
AS
DECLARE @.trhhp DECIMAL(9,2),
@.acp DECIMAL(9,2)
What does trhhp mean? I certainly can't pronounce it, never mind decipher
what it might stand for...
A