June 18, 2004 at 5:08 pm
Hello - I am trying to set the value of 4 variables based on the format of one main variable.
DECLARE @FullOrderNumber VARCHAR(25), @OrderYear VARCHAR(4),
@OrderSeq VARCHAR(6), @Region VARCHAR(2), @NewOrderNumberFormat BIT
IF @FullOrderNumber = '2003-10293-NE' THEN
SELECT @OrderYear ='2003', @OrderSeq = '10293', @Region = 'NE', @NewOrderNumberFormat = 1
IF @FullOrderNumber = '10293-A' THEN
SELECT @OrderYear =NULL, @OrderSeq = '10293', @Region = 'Central', @NewOrderNumberFormat = 0.
I have multiple formats of the @FullOrderNumber (atleast 20 or 25) and based on business logic I need to split them into their respective parts. The IF statement looks too cumbersome. I am wondering if there is a simpler way to do it without using the IF statements...I tried to use the CASE statement but it can only return values, not set multiple variables. Does anyone have any good ideas? Thanks. VN
June 18, 2004 at 6:11 pm
Business logic changes, hopefully with the business. Instead of hard coding the logic create a table that has the values you want to assign using FullOrderNumber as the primary key and join to them. That way you won't have to recode the SP everytime the business changes and neatly solves your select case problem too.
June 18, 2004 at 6:24 pm
Infact, that is what I am trying to do! And, to do that I need to split the Order Number. And, business rules changed! We used to generate order number by Identity column and add a specific character for each region A/B/C. Now, the logic has changed to "Year of the Order"+SequenceNumber+RegionCode (where regioncode = "1NE"/"1SE"/"2SE"/"1SW"). Though the correct format of the new order number is "2003-123456-1SE" some users in the manual system would enter order number as 031234561SE and, I do not know how to split it into the year and sequence number and region code. We are in the process of trying to clean those numbers and any number that cannot be split is either an old format number (which is fine) or wrongly formatted new format number. So, I still need the code that tries to split the Order Number into the correct components without user intervention. If that is not possible I throw an error report and ask the users to fix it. Thanks for responding.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply