Sql Server 2000 Stored Proc not 'seeing' table row

[dfwalton]

I have an app where I need to apply a crew rate based on the date. Crewrate table is very simple: Region, StartDate, EndDate, Fee.

Have a stored proc that needs to get rate, based on the date of the job being done. Select code is:

select event2.EventRegion,'SAM',@newDate, event2.EventSubregion, event2.EventClient,
crewfee.crewfee,
from event as event2, crewFee
where event2.eventnum =@modelEventNum
and @newDate between crewFee.startDate and crewFee.endDate

Crewfee table has 2 rows:
Bay, 2010-04-01, 2011-03-31, 55.75
Bay, 2011-04-01, 2012-03-31, 65.75

Doing a select by itself using where clause above works fine. But in the SP, it fails to find the 65.75 rate for any date, say '05/11/11'

Any ideas?

 

[gmmastros]

Can you copy/paste the stored procedure as it exists right now?

** when you do, please use code tags like this:

[ignore]

Paste your code here

[/ignore]

I suspect there is a problem with your procedure declaration, but would like to see the whole thing just to be sure.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dfwalton]

CREATE PROCEDURE dbo.ProcCreateEventFromAnother2 
	@ModelEventNum int, @NewDate datetime, @newTime datetime= '1/01/1970 12:00PM'
	 
AS
	Declare @NewEventNum int, @success int, @ModelEventShowName varchar(50)
	--select @modelEventNum, @newDate, @newTime
 	--select * from event
 	--where eventNum =  @modelEventNum

	

	IF NOT EXISTS (select * from event
	where eventNum = @modelEventNum)
	Begin
		set @NewEventNum= 0 
		select @NewEventNum as EventNum
		Return
	end

	Select @ModelEventShowName=EventshowName from event where eventnum = @ModelEventNum

	Insert Event(eventregion, 
	eventCompany, 
	eventdate, 
	eventtime, 
	eventsubregion,
	eventclient, 
	eventcrewfee, 
	eventProducer, 
	eventShowName, 
	eventCalName, 
	eventType,
	eventVenue,
	eventEmpRating, 
	Eventstatus, 
	eventPRStatus, 
	EventCrewChiefRate,  
	EventHolidayRates,
	eventDirectBilling,
	eventInvoiceStatus, 
	eventInvNum, 
	HoursConfig, 
	Inactive,
	isSFEvent)

	select  event2.EventRegion,'SAM',@newDate, event2.eventTime,  event2.EventSubregion, event2.EventClient,
	crewfee.crewfee, event2.EventProducer, event2.eventshowName, event2.eventCalName,
	event2.EventType, event2.EventVenue,
	event2.eventEmpRating, 10, 10,event2.EventCrewChiefRate, event2.eventHolidayRates, event2.eventDirectBilling,
	10, NULL, event2.HoursConfig, 0,event2.isSFEvent
	from event as event2,crewFee
	where event2.eventnum =@modelEventNum
	and @newDate between crewFee.startDate and crewFee.endDate

	Select @NewEventNum = max(eventNum) from event
	where rtrim(EventShowName) = rtrim(@ModelEventShowName)  


	Insert EventParticDtl (region, eventNum, empnum, pos, emprate,IsCrewChief, GetsBenefits, PosAlias )
	select region, @NewEventNum, empnum, pos, emprate,IsCrewChief, GetsBenefits,POSALias
	from eventParticDtl
	where eventnum = @modelEventNum
GO

The crewFee table contains two rows. If I use an event date prior to March 31, 2011, the Insert Event clause works fine. If I use a date later than 3/31/11 it fails.

I appreciate your help

 

[markros]

If EventNum is an identity column in the table, then use SCOPE_IDENTITY() function to get the @NewEventNum, e.g.

after insert statement put

set @NewEventNum = SCOPE_IDENTITY()

Also, with the code above you're generating a cross join that may potentially result in many records - is it the intent?



PluralSight Learning Library

 

[dfwalton]

Is it possible that the latest addition to the crew fee table has not been committed? If so, how do I commit it?