Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

write stored procedure with one input parameter, one output parameter Expand / Collapse
Author
Message
Posted Thursday, December 6, 2007 2:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:59 AM
Points: 63, Visits: 98
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())
Post #430430
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse