none
Convert UPC-E to UPC-A upon insert with UDF

    Question

  • I have the function but I need this function to insert on the PosCode in my Products Table:

    CREATE TABLE [Pricebook].[Products] (
        [PosCode]             [dbo].[posCode]        NOT NULL,
        [PosCodeFormat]       AS                     ([dbo].[udf_PosCodeFormat]([PosCode])),
        [CheckDigit]          AS                     ([dbo].[udf_Calculate_UPCA_CheckDigit]([PosCode])),

    where the PosCode is the primary key.  If the the user enters 7 digits then it should convert it to the 11 digit format in that column.  I need the returned output of @upcA to overwrite the data entered in the PosCode column....Here is the function:

    CREATE function dbo.udf_UPCE_to_UPCA (@PosCode varchar(50))
    -- limiting to varchar(8) only reads first 8 characters of any longer strings passed in
    
    returns varchar(12)
    as
    begin
    --Function to take in UPC-E, calculate and return its UPC-A.
    
    -- local variables
    declare @main_six varchar(6) -- The 6 important (middle) digits of UPCE
    declare @upcA varchar(12) -- Calculated UPCA
    
    -- Iniial settings
    set @PosCode = ltrim(rtrim(isnull(@PosCode,'')))
    set @upcA = ''
    
    -- Calculate UPC-A
    if (-- Required conditions for conversion: length must be 8, must start with 0 or 1
    len(@PosCode) = 8
    and left(@PosCode,1) in ('0','1')
    and isnumeric(@PosCode) = 1
    )
    begin
    set @main_six = substring(@PosCode,2,6)
    
    if (right(@main_six,1) in ('0','1','2'))
    begin
    set @upcA = left(@PosCode,1)
    + left(@main_six,2)
    + right(@main_six,1)
    + '0000'
    + substring(@main_six,3,3)
    + right(@PosCode,1)
    end
    
    else if (right(@main_six,1) = '3')
    begin
    set @upcA = left(@PosCode,1)
    + left(@main_six,3)
    + '00000'
    + substring(@main_six,4,2)
    + right(@PosCode,1)
    end
    
    else if (right(@main_six,1) = '4')
    begin
    set @upcA = left(@PosCode,1)
    + left(@main_six,4)
    + '00000'
    + substring(@main_six,5,1)
    + right(@PosCode,1)
    end
    
    else
    begin
    set @upcA = left(@PosCode,1)
    + left(@main_six,5)
    + '0000'
    + substring(@main_six,6,1)
    + right(@PosCode,1)
    end
    end -- main if: Required conditions
    return @upcA
    end
    Any help is greatly appreciated...thanks to all who offer it.
    Friday, May 16, 2014 2:24 AM

Answers

  • Let's take a step back.  You are using an user-defined type for PosCode in your table - and it would help to see the actual definition of the type.  Why? Because your function signature, code, and description are more than a little fuzzy and a lack of clarity / precision is generally bad - especially when dealing with primary keys!.  Another example - your function returns varchar(12) [sidenote - variable length?  really?] but the input argument is varchar(50) and you have a comment that says "limiting to varchar(8)".  Your text says that the function should "convert it to the 11 digit format " - where did the extra CHARACTER come from in the return value?

    Next, check your function code very carefully.  You need to think about what happens when something is provided as the input argument that does not match your expectations.  That first IF statement will only execute one of those nested if/else blocks for a limited set of values.  Everything else will be ignored and the default return value of an empty string will be returned.  Let the database engine prevent nonsense data in the first place.  If you only want to consider 8 characters for input then you should define the input argument datatype as char(8) - I don't see any reason to make it variable. That will prevent anyone from providing anything more than 8 characters (but not less).

    Next, you want to transform the value of PosCode - which is the primary key for the table - from the value provided (by a user or other system etc.) to something that meets a set of criteria.  Your system "knows" that PosCode is the PK of the table, so imagine how an application should react when it enters a value of 'X' and then attempts to retrieve this row from the table - only to find that the row does not exist because the back-end has transformed the PK value that identifies it.  How will the application know that the value 'X' supplied during insert is now 'Y' after transformation. 

    Before you go further you should give some additional thought to this approach.  I think you should consider "validating" the value entered as PosCode in some fashion rather than transforming it since it will be difficult to implement an application that should understand the "x becomes y" issue mentioned above. I doubt you can do this in a useful manner with the actual primary key.  Perhaps there is a better and different approach - I can't say since that requires a better understanding of what data is entered, what is expected of that data in term of validity, the reasons for your transformation, etc.

    • Proposed as answer by Naomi NModerator Friday, May 16, 2014 4:02 PM
    • Marked as answer by Sean042 Tuesday, May 20, 2014 3:28 AM
    Friday, May 16, 2014 2:05 PM

All replies

  • It sounds like you want to use INSTEAD OF INSERT trigger.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, May 16, 2014 2:41 AM
  • I tried and I can't get it.  ANy help?
    CREATE TRIGGER [Pricebook].[tr_UPCEtoUPCA]
    	ON [Pricebook].[Products]
    	INSTEAD OF INSERT
    	AS
    	BEGIN
    
    declare @main_six varchar(6) -- The 6 important (middle) digits of UPCE
    declare @PosCode varchar(24) -- Calculated UPC
    declare @iPosCode varchar(8) -- 8 digit insert
    
    set @iPosCode = (select @PosCode from inserted)
    
    -- Calculate UPC-A
    if (-- Required conditions for conversion: length must be 8, must start with 0 or 1
    len(@iPosCode) = 8
    and left(@iPosCode,1) in ('0','1')
    and isnumeric(@iPosCode) = 1
    )
    begin
    set @main_six = substring(@iPosCode,2,6)
    
    if (right(@main_six,1) in ('0','1','2'))
    begin
    select @PosCode = left(@iPosCode,1)
    + left(@main_six,2)
    + right(@main_six,1)
    + '0000'
    + substring(@main_six,3,3)
    + right(@iPosCode,1)
    end
    
    else if (right(@main_six,1) = '3')
    begin
    select @PosCode = left(@iPosCode,1)
    + left(@main_six,3)
    + '00000'
    + substring(@main_six,4,2)
    + right(@iPosCode,1)
    end
    
    else if (right(@main_six,1) = '4')
    begin
    select @PosCode = left(@iPosCode,1)
    + left(@main_six,4)
    + '00000'
    + substring(@main_six,5,1)
    + right(@iPosCode,1)
    end
    
    else
    begin
    select @PosCode = left(@iPosCode,1)
    + left(@main_six,5)
    + '0000'
    + substring(@main_six,6,1)
    + right(@iPosCode,1)
    end
    begin
    insert into Products
    values (@PosCode)
    --set PosCode = @PosCode
    end
    end -- main if: Required conditions

    Friday, May 16, 2014 1:23 PM
  • Perhaps, the following

    set @iPosCode = (select PosCode from inserted)

    Also at end, while inserting row into table you have to populate all other column values too.

    insert into Products
    values
    (@PosCode,...)

    • Edited by KEAARPEE Friday, May 16, 2014 1:39 PM
    Friday, May 16, 2014 1:36 PM
  • Let's take a step back.  You are using an user-defined type for PosCode in your table - and it would help to see the actual definition of the type.  Why? Because your function signature, code, and description are more than a little fuzzy and a lack of clarity / precision is generally bad - especially when dealing with primary keys!.  Another example - your function returns varchar(12) [sidenote - variable length?  really?] but the input argument is varchar(50) and you have a comment that says "limiting to varchar(8)".  Your text says that the function should "convert it to the 11 digit format " - where did the extra CHARACTER come from in the return value?

    Next, check your function code very carefully.  You need to think about what happens when something is provided as the input argument that does not match your expectations.  That first IF statement will only execute one of those nested if/else blocks for a limited set of values.  Everything else will be ignored and the default return value of an empty string will be returned.  Let the database engine prevent nonsense data in the first place.  If you only want to consider 8 characters for input then you should define the input argument datatype as char(8) - I don't see any reason to make it variable. That will prevent anyone from providing anything more than 8 characters (but not less).

    Next, you want to transform the value of PosCode - which is the primary key for the table - from the value provided (by a user or other system etc.) to something that meets a set of criteria.  Your system "knows" that PosCode is the PK of the table, so imagine how an application should react when it enters a value of 'X' and then attempts to retrieve this row from the table - only to find that the row does not exist because the back-end has transformed the PK value that identifies it.  How will the application know that the value 'X' supplied during insert is now 'Y' after transformation. 

    Before you go further you should give some additional thought to this approach.  I think you should consider "validating" the value entered as PosCode in some fashion rather than transforming it since it will be difficult to implement an application that should understand the "x becomes y" issue mentioned above. I doubt you can do this in a useful manner with the actual primary key.  Perhaps there is a better and different approach - I can't say since that requires a better understanding of what data is entered, what is expected of that data in term of validity, the reasons for your transformation, etc.

    • Proposed as answer by Naomi NModerator Friday, May 16, 2014 4:02 PM
    • Marked as answer by Sean042 Tuesday, May 20, 2014 3:28 AM
    Friday, May 16, 2014 2:05 PM
  • What you wrote makes very little sense. I was thinking about

    ;with cte as (select *, udf(PosCode) as CorrectedPosCode from Inserted)

    insert into RealTable (col1, col2, ..., PosCode)

    select Col1, Col2, ..., CorrectedPosCode from cte


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, May 16, 2014 4:01 PM