Stored Procedure Questions

[lisa626]

I am VERY new to this. I have SQL 2000, there is a stored procedure that was previously created that I need to be able to run, I made some minor changes to it such as I want it to SPECIFY a date to look out but am not sure I have done that right either.

Below is the code, like I said I really have no clue about this, what I do know is I want to run this query for one day to make sure it works then run for the whole month. Any help on this would be GREATLY appreciated.
___________________________________________________________
CREATE PROCEDURE [dbo].[DTS_FIX_HTLSEGS]
@startdate as datetime,
@enddate as datetime

AS

set @startdate = '10/01/2006 00:00:00'
set @enddate = '10/01/2006 11:59:59'

/** set dates for htlseg invoices
set @startdate = '10/01/2006 00:00:00'
set @enddate = '10/01/2006 11:59:59'
**/

update btmcuser.htlsegs set division = ' ' where division is null
and INV_DATE >= @startdate and INV_DATE <= @enddate

update btmcuser.htlsegs set dept = ' ' where dept is null
and INV_DATE >= @startdate and INV_DATE <= @enddate

update btmcuser.htlsegs set full_company = company_code + division + dept where
btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

update btmcuser.htlsegs
Set Full_Name = City_Code + [Name],
HtlPreferred = 0, CorpRate = 0, BoeingLowRate = 0,
BoeingHighRage = 0, ContractRate = 0, HtlName= '',
HtlPhone = '', HtlAddr1 = '', HtlAddr2 = '',
HtlStateCode = '', HtlCity = '', HtlAirportName = '',
HtlIntlDom = 'D', HtlAirportState = '', Void_Count = 0,
SvcFee_Count = 0, PH = '', hBems_ID = 0, hWork_City = '',
hWork_State_CD = '', Brand_Name = '', HtlZipCode = ''
where btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

Update btmcuser.htlsegs
Set HtlAirportName = btmcuser.airport.[Name], HtlAirportState = btmcuser.airport.[State]
From btmcuser.htlsegs, btmcuser.airport where City_Code = code
and btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

Update btmcuser.htlsegs
Set htlPhone = substring(Phone,1,3) + substring(Phone,4,3) + substring(Phone,7,4),
htlName = Address_1, htlAddr1 = Address_2, htlAddr2 = '', HtlCity = City,
HtlStateCode = State, HtlIntlDom = IntlDom from btmcuser.htlsegs, btmcuser.hotels
where btmcuser.htlsegs.hotel_uniqueid = btmcuser.hotels.uniqueid
and btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

Update btmcuser.htlsegs
Set corprate = hotelrfp.us_rate, ContractRate = high, HtlPreferred = 1
From btmcuser.htlsegs, HotelRFP
where HtlPhone = full_Phone and Company_Code Like '1%'
and btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

Update btmcuser.htlsegs
Set corprate = hotelrfp.us_rate, ContractRate = high, HtlPreferred = 1
From btmcuser.htlsegs, HotelRFP
where HtlPhone = full_Phone and Not Company_Code Like '1%' and UserDefined5 = 'Y'
and btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

UPDATE btmcuser.htlsegs SET Brand_name = Case
WHEN Chain_Code IN ('MC', 'RC', 'CY', 'BR', 'FN', 'TO', 'XV', 'RZ') then 'Marriott'
WHEN Chain_Code IN ('DT', 'ES', 'HH', 'HL', 'RL', 'GX', 'HG', 'CM', 'SH') then 'Hilton'
WHEN Chain_Code IN ('WI', 'SI', 'WH', 'LC') then 'Starwood'
WHEN Chain_Code IN ('RI', 'FZ', 'QI', 'CC', 'CI', 'SZ', 'EO', 'MZ', 'CZ', 'FW') then 'Choice'
WHEN Chain_Code IN ('HI', 'IC', 'CP', 'YZ') then 'Six Continents'
WHEN Chain_Code = 'RD' then 'Radisson'
WHEN Chain_Code = 'HY' then 'Hyatt'
WHEN Chain_Code = 'BW' then 'Best Western'
Else 'Other' END
where btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

UPDATE btmcuser.htlsegs SET CorpRate = US_RATE / .92
WHERE (CHAIN_CODE = 'MC') AND (COMPANY_CODE = '1001') AND (HtlPreferred = 0) and HtlIntlDom = 'D'
and btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

update btmcuser.htlsegs set hWork_State_CD = '' where hWork_State_CD Is Null
and btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

update btmcuser.htlsegs set hwork_city = '' where hwork_city Is Null
and btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

update btmcuser.htlsegs set hBEMS_ID = '' where hBEMS_ID Is Null
and btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

UPDATE btmcuser.htlsegs SET
hwork_city = work_city, hwork_State_CD = btmcuser.tickets.work_State_CD,
hBEMS_ID = btmcuser.tickets.BEMS_ID FROM btmcuser.htlsegs, btmcuser.tickets
WHERE Ticket_No = TicketNo and hwork_city Is Null
and btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate

UPDATE btmcuser.htlsegs SET ABU = btmcuser.tickets.ABU
FROM btmcuser.htlsegs JOIN btmcuser.tickets ON btmcuser.tickets.ticketno = btmcuser.htlsegs.ticket_no
WHERE btmcuser.htlsegs.abu is null and btmcuser.tickets.company_code = '1001' AND len(btmcuser.tickets.abu) = 2
and btmcuser.htlsegs.INV_DATE >= @startdate and btmcuser.htlsegs.INV_DATE <= @enddate
GO

 

[AlexCuse]

Have you tried running this yet? As it involves updates make sure you have a good backup first. It is really a lot to ask of anyone to break down all that code without even a clue of where things are wrong.

One thing I notice is that you shouldn't be setting start date and end date in code, if they are to be passed in as parameters.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom