Post reply

Stored Procedure Output Parameter

  • June 4, 2008 at 10:57 am

    #188510

    A stored procedure has an output parameter declared as:

    CREATE procedure dbo.usp_hrs_calculate_benefit

    @subjectto decimal(15,2),

    @frequency int,

    @bendeddescid int,

    @emprolebendedid int,

    @enddate datetime,

    @employeramount decimal(12,2) OUTPUT

    as

    .

    .

    .

    When executing the sp we get the error:"Error converting datatype decimal to numeric"

    I have isolated the error to the output parameter.

    When looking at the execution via Profiler I noticed that the declare for the output parameter prior to the sp call changed the precision from 12,2 as defined to 18,14. Which causes the error.

    I am calling the sp from a PB app and have coded a solution. But am very curious as to why the scale/precision changed? I would much prefer a SQL solution if there is one. Is there?

    Thanks,

    Mick

  • June 4, 2008 at 11:40 am

    #824380

    Could you be trying to fit a value into your output parameter that is too large?

  • June 4, 2008 at 11:46 am

    #824387

    Yes. The value that the output parameter is set to is too large because SQL changes the scale. My declaration of 12,2 is plenty but the change to 18,14 causes the trouble.

  • June 4, 2008 at 12:07 pm

    #824408

    I'm guessing, without the rest of the proc, but I bet there's a line in there that assigns a value to the parameter and uses either a malformed explicit conversion, or an implicit conversion.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • June 4, 2008 at 12:12 pm

    #824411

    When looking at the execution via Profiler I noticed that the declare for the output parameter prior to the sp call changed the precision from 12,2 as defined to 18,14.

  • June 4, 2008 at 12:51 pm

    #824446

    Please show us the calling SQL code.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

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

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