SQL repetition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

SQL repetition

Author

Topic

Kyloso
Starting Member

2 Posts

Posted - 2007-08-02 : 11:10:40

Hello all, i am coding a SP for Sql and am having trouble with a portion of the code.

I have two nested while loops, the main loop for units and the inner loop for equipment.

The info is written to a temp table, but the last record in the temp table is always duplicated.

Thanks for all your help

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-02 : 11:34:23

loop one time less...

seriously though. without your code we can't help you.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kyloso
Starting Member

2 Posts

Posted - 2007-08-02 : 11:41:40

Alter Procedure SP_DD_UNITS_EXPORT

As

Set nocount on

--Creating a Temp Table
create table tblDDEXPORT
(
DD_Unit_Number varchar(10) null,
Unit_Number varchar(15) null,
Unit_Info varchar(max) null,
License_# varchar(10) null,
ME_Info varchar(max) null,
Region varchar(10) null,
Division varchar(10) null,
Vehicle_Type varchar(10) null,
Type_Class_SubClass varchar(15) null,
serial_# varchar(25) null,
Date DateTime null,
Tare varchar(10) null,
GVW varchar(10) null,
Tire_Size varchar(115) null,
Model_Year varchar(5) null,
One int null,
)

--Declaring the cursor for the initial data input
declare cur_Export_Fields_Except_ME_Info Cursor
For

select distinct 'E' + vehicles_fw.Access_Unitid_fw as DD_ID, vehicles_fw.vehicle_id_fw, Convert(varchar, vehicles_fw.model_year_fw),
manufacturers_fw.Description_fw, models_fw.description_fw, vehicles_fw.certificate_number_fw, depots_fw.depot_id_fw, vehicles_fw.costcentre_fw,
national_vehicle_types_fw.description_fw, vehicles_fw.national_vehicle_type_fw + '_' + user_defined8_fw.class_alias_fw + '_' + user_defined9_fw.subclass_alias_fw 'DD Veh. Info',
vehicles_fw.chassis_number_fw, vehicles_fw.in_service_date_fw, vehicles_fw.weightulw_fw, vehicles_fw.weightgvw_fw, 'F: ' + vehicles_fw.Tyre_Size_Front_fw + ' B: ' + vehicles_fw.Tyre_Size_Rear_fw,
vehicles_fw.model_year_fw, 1, convert(varchar(max), Vehicles_fw.Comments_fw)

from vehicles_fw left outer join manufacturers_fw on vehicles_fw.make_code_fw = manufacturers_fw.code_fw left outer join
models_fw on vehicles_fw.model_code_fw = models_fw.model_code_fw left outer join user_defined8_fw on vehicles_fw.dd_class_fw =
user_defined8_fw.code_fw left outer join user_defined9_fw on vehicles_fw.dd_subclass_fw = user_defined9_fw.code_fw left outer join
national_vehicle_types_fw on vehicles_fw.National_vehicle_type_fw = national_vehicle_types_fw.Code_fw left outer join
customers_fw on vehicles_fw.costcentre_fw = customers_fw.Customer_fw left outer join depots_fw on customers_fw.depot_id_fw =
depots_fw.depot_id_fw

where vehicles_fw.archive_status_fw = 'N' and vehicles_fw.vehicle_status_fw = 'DEPLOY' --This statemnt lowers the procedure to returning only
--vehicles with a model: and (Models_fw.description_fw is not null or len(rtrim(ltrim(Models_fw.description_fw))) != 0)

group by vehicles_fw.vehicle_id_fw, vehicles_fw.Access_Unitid_fw, vehicles_fw.model_year_fw,
manufacturers_fw.Description_fw, models_fw.description_fw, vehicles_fw.certificate_number_fw, depots_fw.depot_id_fw, vehicles_fw.costcentre_fw,
national_vehicle_types_fw.description_fw, vehicles_fw.chassis_number_fw, vehicles_fw.in_service_date_fw, vehicles_fw.weightulw_fw,
vehicles_fw.weightgvw_fw, vehicles_fw.Tyre_Size_Front_fw, vehicles_fw.Tyre_Size_Rear_fw, vehicles_fw.model_year_fw, user_defined8_fw.class_alias_fw,
user_defined9_fw.subclass_alias_fw, vehicles_fw.National_vehicle_type_fw, convert(varchar(max), Vehicles_fw.Comments_fw)

order by vehicles_fw.vehicle_id_fw

--Opening the cursor to fetch from
Open cur_Export_Fields_Except_ME_Info

--Declaring Variables for input into the table and declaring the fetch statuses.
Declare @DDUnitID varchar(10), @UnitID Varchar(15), @Year varchar(5), @Make varchar(15), @Model varchar(15), @License# varchar (10), @Region varchar(10), @Division varchar(10),
@VehicleType varchar(10), @TypeClassSubClass varchar(15), @Serial# varchar(25), @Date datetime, @Tare varchar(10), @GVW varchar(10),
@TireSize varchar(15), @Model_Year varchar(5), @One int, @FETCH_STATUS int, @UnitInfo varchar(40), @UnitComments varchar(max),

--variables for ME information
@ME_INFO varchar(max), @BULK_ME_INFO varchar(max), @ME_FETCH_STATUS int, @MEYear varchar(5), @MEMake varchar(15), @MESerialNumber varchar(20), @MEClass varchar(10),
@MESpec varchar(10), @MENotes varchar(150)

--Setting the fetch status to 0, so the loop can begin
set @FETCH_STATUS = 0
While @FETCH_STATUS = 0
BEGIN
-- fetching data from the cursor and placing it into the variables
Fetch from cur_Export_Fields_Except_ME_Info into @DDUnitID, @UnitID, @Year, @Make, @Model, @License#, @Region, @Division,
@VehicleType, @TypeClassSubClass, @Serial#, @Date, @Tare, @GVW,
@TireSize, @Model_Year, @One, @UnitComments
--Setting Fetch Status to the global variable @@Fetch_Status
Set @FETCH_STATUS = @@FETCH_STATUS
--Initializing Unit Info to be blank
set @UnitInfo = ''
--Insuring that UnitInfo Data is not blank or null
if(@Year = 0)
set @UnitInfo = ''
else
set @UnitInfo = @UnitInfo + @Year
if(@Make is null)
set @UnitInfo = @UnitInfo + ''
else
set @UnitInfo = @UnitInfo + ' ' + @Make
if(@Model is null)
set @UnitInfo = @UnitInfo + ''
else
set @UnitInfo = @UnitInfo + ' ' + @Model

--Declaring Cursor for Input of ME Info
declare cur_ALL_ME_Information_per_Unit Cursor
For
(
--Cannot sort/compare with text or ntext data types so forced to convert
select distinct convert(varchar, user_defined20_fw.model_year_fw), convert(varchar, manufacturers_fw.description_fw), convert(varchar, user_defined20_fw.serial_number_fw),
convert(varchar, user_defined20_fw.Equip_Class_id_fw), convert(varchar, user_defined20_fw.spec_id_fw), convert(varchar, User_Defined20_fw.Notes_fw)

from user_defined20_Fw left outer join manufacturers_fw on user_defined20_fw.equipment_make_fw =
manufacturers_fw.code_fw left outer join vehicles_fw on user_defined20_fw.Vehicle_id_fw = vehicles_fw.vehicle_id_fw

where user_defined20_fw.archive_status_fw = 'N' and user_defined20_fw.Date_Mounted_Equipment_Removed_fw is null and
user_defined20_fw.Date_Mounted_Equipment_Added_fw is not null and user_defined20_Fw.vehicle_id_fw = @UnitID
)

-- Open cursor to fetch ME Info from
Open cur_ALL_ME_Information_per_unit
--Initialize Bulk ME Info to be blank
set @BULK_ME_INFO = ''
--Setting ME FETCH STATUS so the loop can begin.
set @ME_FETCH_STATUS = 0
While @ME_FETCH_STATUS = 0
BEGIN
-- Initializing ME Information to be blank
Select @MEYear = 0, @MEMake = null, @MESerialNumber = '', @MEClass = '', @MESpec = '', @MENotes = '', @ME_INFO = ''

-- fetching data from the cursor and placing it into the variables
Fetch from cur_ALL_ME_Information_per_unit into @MEYear, @MEMake, @MESerialNumber, @MEClass,
@MESpec, @MENotes
--Setting fetch status to ensure the cursor is not already emp
set @ME_FETCH_STATUS = @@FETCH_STATUS

-- Placing the populated ME Information into a single variable
If (@MEYear != 0)
set @ME_INFO = @ME_INFO + ' Year: ' + @MEYear
if (@MEMake is not null)
set @ME_INFO = @ME_Info + ' Make: ' + @MEMake
if(len(ltrim(rtrim(@MESerialNumber))) != 0)
set @ME_INFO = @ME_INFO + ' Serial #: ' + @MESerialNumber
if(len(ltrim(rtrim(@MEClass))) != 0)
set @ME_INFO = @ME_INFO + ' ME Class: ' + @MEClass
if(len(ltrim(rtrim(@MESpec))) != 0)
set @ME_INFO = @ME_INFO + ' ME Spec: ' + @MESpec
if(len(ltrim(rtrim(@MENotes))) != 0)
set @ME_INFO = @ME_INFO + ' Notes: ' + @MENotes

--Adding the ME_Info into the bulk ME info for final display
Select @BULK_ME_INFO = @BULK_ME_INFO + ' ' + @ME_INFO

END
--Checking to see if any of the unit's have expired inspections/permits.
-- inspections for Unit
IF( SELECT Count(*)
FROM dbo.VEHICLES_FW INNER JOIN dbo.USER_DEFINED19_FW ON dbo.VEHICLES_FW.VEHICLE_ID_FW = dbo.USER_DEFINED19_FW.VEHICLE_ID_FW
INNER JOIN dbo.USER_DEFINED12_FW ON dbo.USER_DEFINED19_FW.INSP_TYPE_FW = dbo.USER_DEFINED12_FW.CODE_FW
WHERE USER_DEFINED19_FW.NEXT_INSP_DATE_FW < getdate()
GROUP BY vehicles_fw.vehicle_id_fw
Having convert(varchar, vehicles_fw.vehicle_id_fw) = @UnitID)

> 0 or

-- Inspections for Mounted Equipment as related to its’ current unit
(SELECT count(*)
FROM dbo.USER_DEFINED20_FW INNER JOIN
dbo.USER_DEFINED19_FW AS USER_DEFINED19_FW_1 ON
dbo.USER_DEFINED20_FW.ACCESS_EQUIP_FW = USER_DEFINED19_FW_1.ACCESS_EQUIP_FW INNER JOIN
dbo.VEHICLES_FW AS VEHICLES_FW_1 ON dbo.USER_DEFINED20_FW.VEHICLE_ID_FW = VEHICLES_FW_1.VEHICLE_ID_FW INNER JOIN
dbo.USER_DEFINED12_FW AS USER_DEFINED12_FW_1 ON
USER_DEFINED19_FW_1.INSP_TYPE_FW = USER_DEFINED12_FW_1.CODE_FW
WHERE (dbo.USER_DEFINED20_FW.DATE_MOUNTED_EQUIPMENT_ADDED_FW IS NOT NULL) AND
(dbo.USER_DEFINED20_FW.DATE_MOUNTED_EQUIPMENT_REMOVED_FW IS NULL) and USER_DEFINED19_FW_1.NEXT_INSP_DATE_FW <
getdate()
group by vehicles_fw_1.vehicle_id_fw
Having convert(varchar, vehicles_fw_1.vehicle_id_fw) = @UnitID)

> 0 or

-- Permits for Unit
(SELECT count(*)
FROM USER_DEFINED13_FW inner join
VEHICLES_FW ON USER_DEFINED13_FW.VEHICLE_ID_FW = VEHICLES_FW.VEHICLE_ID_FW
WHERE USER_DEFINED13_FW.Last_Permit_Date_FW <
getdate()
group by vehicles_fw.vehicle_id_fw
Having convert(varchar, vehicles_fw.vehicle_id_fw) = @UnitID)

> 0
--Setting **EXP** into the unit id column to notify DD of an expired unit level permit, ME Inspection, or unit inspection.
Set @UnitID = @UnitID + ' **EXP**'

--Adding the Unit Comments to the Bulk_ME_Info variable
IF(@UnitComments is not null and len(ltrim(rtrim(@UnitComments))) != 0)
set @BULK_ME_INFO = ('Unit Comments: ' + @UnitComments) + ' ' + @BULK_ME_INFO

set @UnitComments = ''
--Inserting the data from the variables into the DDEXPORT temp table
insert into tblDDEXPORT (DD_Unit_Number, Unit_Number, Unit_Info, License_#, ME_INFO, Region, Division, Vehicle_Type, Type_Class_SubClass, Serial_#, Date, Tare,
GVW, Tire_Size, Model_Year, One)
values (@DDUnitID, @UnitID, @UnitInfo, @License#, @BULK_ME_INFO, @Region, @Division, @VehicleType,@TypeClassSubClass, @Serial#, @Date, @Tare, @GVW, @TireSize,
@Model_Year, @One)
if @@Error != 0
Begin
RAISERROR('Error inserting data into the tblDDEXPORT temp table', 16, 1)
End
--Closing ME information cursor
Close cur_ALL_ME_Information_per_unit
--Dropping the cursor used for ME info
Deallocate cur_ALL_ME_Information_per_unit

End
--Close Initial Data Cursor
Close cur_Export_Fields_Except_ME_Info
-- Dropping the cursor used for the bulk of the export fields
Deallocate cur_Export_Fields_Except_ME_Info

select *
from tblDDEXPORT
order by UNIT_NUMBER
--Dropping Temp Table
drop table tblDDEXPORT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources