mercury76
Starting Member
7 Posts |
Posted - 2007-08-28 : 23:52:39
|
Hi guys,I am new to programming and sqlserver 2000. i use sql2000 as my database server...I have a problem on auto-generating medicine codes. and i hope someone would help me with my script.as you noticed i declared two variables (@id and @code). I am having a problem in generating @code... i would just like to create an autogenerated medicines_code from another field from another table (hosp_counter.counter_id)here is how my medicines_code should look like ('MEDS34') "where 34 is from a counter_id in hosp_counter table...but it only creates a medicines_code = 'M' (below is the result) 34(this is from counter_id) M(this is the autogenerated code) AUTOGENERATE1 1 1 1 1 0 1 0 1 1 0 0 1 2 1 100 100 0 0 0 0 0 0 1 8/29/2007 11:29:46 AM 1/1/1900 1/1/1900here is my script in autogenerating medicine code... set xact_abort onbegin transactiondeclare @Code as varcharselect @Code =(select 'MEDS' + CAST(Counter_ID + 1 as varchar (50))from Hosp_Counter where table_description = 'Medicines')declare @ID as integerselect @ID = Counter_ID + 1 from Hosp_Counter where table_description = 'Medicines' select @ID INSERT INTO Hosp_Medicines(medicines_id,medicines_code,medicines_description,medicines_specification,medicines_genericname,medicines_drugclass,PhicCategory,medpreparation,medicines_prohibited,medicines_trade,medicines_NoneTrade,medicines_taxable,medicines_PhicCovered,medicines_AmountCovered,medicines_Discountable,medicines_SmallUnit,medicines_BigUnit,medicines_AverageCostSmall,medicines_AverageCostBig,medicines_WeightedAverageCost,medicines_weight,medicines_conversion,medicines_LowStockLevel,medicines_OverStockLevel,medicines_LowSalesReference,AllowStat,status,CreateBy,UpdateBy,DeleteBy,CreateDate,UpdateDate,DeleteDate)VALUES(@ID,@code,'AUTOGENERATE2','',1,1,1,1,0,1,0,1,1,0,0,1,2,1,100,100,0,0,0,0,0,0,1,'','','','8/29/2007 11:31:25 AM','','')select @IDUpdate Hosp_Counter set Counter_ID = @IDwhere Table_Description = 'Medicines'commit transactionany help is very much appreciated!'confused'Joel |
|