Rank: Member
Joined: 11/25/2003(UTC)
Posts: 370
|
Had someone unable to add offers and have traced it to this stored procedure call below. Question if anyone has seen this issue before, does the return here need to be a tinyint. If set to int will it cause issue elsewhere? I have not figured out the significance or the [order] column in the offers table as of yet. The [order] column in dbo.bvc_Offers is int yet this sproc shown returns a tinyint. So, in this case we have an offer with an [order] value of 256 and the sproc bombs with an overflow. Code:
ALTER PROCEDURE [dbo].[bvc_Offer_HighestOrderByPriority_s]
@priority tinyint
AS
DECLARE @returnValue tinyint
BEGIN TRY
SET @returnValue = (SELECT COALESCE(MAX([Order]),1) FROM bvc_Offers WHERE Priority = @priority)
END TRY
BEGIN CATCH
EXEC bvc_EventLog_SQL_i
END CATCH
RETURN @returnValue
|
|
|
|
|
|
|
Rank: Administration
Joined: 4/2/2004(UTC) Posts: 2,393 ![United States]( "United States") Location: Hummelstown, PA Thanks: 6 times
Was thanked: 163 time(s) in 158 post(s)
|
Can't say we've run into this one before, but that's probably because none of our clients have that many offers. Looks like you found a bug! |
Aaron Sherrick
BV Commerce
Toll-free 888-665-8637 - Int'l +1 717-220-0012 |
|
|
|
|
|
Rank: Member
Joined: 11/25/2003(UTC)
Posts: 370
|
:) Yep, this client loves the Buy One - Get One offers.
So, trying to figure out why we need a tinyint in this sproc and what happens if I change the return to int. |
|
|
|
|
|
|
Rank: Member
Joined: 11/5/2003(UTC)
Posts: 1,786
|
This is a known issue and I believe we corrected in a 5.7. Is your client running 5.7?
|
|
|
|
|
|
Rank: Member
Joined: 11/25/2003(UTC)
Posts: 370
|
No, 5.4 Rev 5 is the version used.
What exactly does that [order] column do? |
|
|
|
|
|
|
Rank: Member
Joined: 11/5/2003(UTC)
Posts: 1,786
|
Some offers need to by run in a specific order to apply correctly. The order column was used to determine that sequence. However, it was incorrectly used during one service pack as a sort order, limiting promotions to 256 total. It was corrected in 5.6 or 5.7. It's not just a DB change, that actual code used a small int value too so it requires a core code update.
|
|
|
|
|
|
Rank: Member
Joined: 11/25/2003(UTC)
Posts: 370
|
I just looked at a 5.7.1 version and the sproc is exactly the same. So if it is incremented as you add offers you would bomb out again at 256. Also, On this 5.4 version playing with a local copy I changed the sproc to return an int and offers do seem to be working fine. I cannot find limitations in the 5.4 Core code that would cause issue changing the sproc return an int or using these values during processing offers when an item is added to the cart. I do see the use of byte for priority in the code but every reference I am finding to order is using int. So I am thinking I may be good to go with the sproc change. Public Class Offer Code:
Private _priority As Byte = 0
Private _order As Integer = -1
|
|
|
|
|
|
|
Rank: Member
Joined: 11/5/2003(UTC)
Posts: 1,786
|
Okay, maybe I misunderstood the question. I know we had an issue with _priority being a byte but I think I confused _priority and _order in your original question.
|
|
|
|
|
|
Rank: Member
Joined: 11/25/2003(UTC)
Posts: 370
|
Thanks Marcus. Yes, I do see the case of ordering the execution with the Priority setting. |
|
|
|
|
|
|
Rank: Member
Joined: 11/25/2003(UTC)
Posts: 370
|
So, changing the sproc to return int instead of tinyint seems to be working out well. May want to include this adjustment on the next service pack. Not that many would ever get hit by it :) Code:
CREATE PROCEDURE [dbo].[bvc_Offer_HighestOrderByPriority_s]
@priority TINYINT
AS
DECLARE @returnValue INT
BEGIN TRY
SET @returnValue = (SELECT COALESCE(MAX([Order]),1) FROM bvc_Offers WHERE Priority = @priority)
END TRY
BEGIN CATCH
EXEC bvc_EventLog_SQL_i
END CATCH
RETURN @returnValue
|
|
|
|
|
|
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.