write stored procedure with one input parameter, one output parameter

  • Hello all,

    I hope I ask this question correctly, and with the information necessary to help adequate help.

    Need to retrieve the next available number. Receive a file with a list of tracking numbers.

    Each number is 22 characters long. Example: 3027777777450580000001

    Position 14 and 15 = service level code in this case: 58

    The service level will always be 2 characters position 14 and 15 within the number.

    I will insert the contents of this file into a sql server table name estafeta_numbers

    There are three types of status:

    A = active

    N = not active

    X = already used

    When the file is inserted the first time, all the numbers will have a status of 'N' and the last_modified_date will be = create_date

    When a new tracking number is retrieved its status needs to be updated to 'A' and the one that was used before its status will change to 'X'

    The stored procedure that i need help with, will receive input parameter: Service Level, and return output parameter: Tracking Number

    Below I paste sample table and data.

    Thank you much,

    table structure:

    CREATE TABLE t_estafeta_range

    (id int IDENTITY(1,1),

    estafeta_tracking_number varchar(22) not null,

    created_date datetime not null,

    last_tracking_number_used varchar(22) null,

    status varchar(1) not null,

    last_modified_date datetime not null)

    Dummy Data:

    INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000001', getdate(), NULL, 'N', getdate())

    INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000002', getdate(), NULL, 'N', getdate())

    INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000003', getdate(), NULL, 'N', getdate())

    INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000004', getdate(), NULL, 'N', getdate())

    INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000005', getdate(), NULL, 'N', getdate())

    INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000006', getdate(), NULL, 'N', getdate())

    INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000007', getdate(), NULL, 'N', getdate())

    INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000008', getdate(), NULL, 'N', getdate())

    INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000009', getdate(), NULL, 'N', getdate())

    INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000010', getdate(), NULL, 'N', getdate())

Viewing 0 posts

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