April 11, 2008 at 8:24 am
Hi,
I'm having problems with the below sp. It supposed to insert either the whole transaction or nothing, but at the moment it's just inserting the first insert statment.
Many thanks in advance
Dave
ALTER procedure [dbo].[sp_tmptimesheet_insert_day]
(@weektotal int,
@workedon datetime,
@hoursworked numeric(10, 2),
@UserNM int,
@hoursworkedtue numeric(10, 2),
@hoursworkedwed numeric(10, 2),
@hoursworkedthurs numeric(10, 2),
@hoursworkedfri numeric(10, 2),
@hoursworkedsat numeric(10, 2),
@hoursworkedsun numeric(10, 2),
@createduserid int,
@issued Nvarchar(1),
@readyforbilling Nvarchar(1),
@performancevalueid int,
@rate Nvarchar)
as
BEGIN TRY
BEGIN TRANSACTION
insert INTO tmptimesheets
( PlacementID
, Periodstarting
, createdon
, createduserid
,issued
,readyforbilling
,rate)
SELECT placementid
, @workedon
, getdate()
,@createduserid
,@issued
,@readyforbilling
,@rate
FROM sql03.pronet_ts.dbo.placements
WHERE applicantid = @userNM
AND enddate > dateadd(week,-2,getdate()) and @weektotal > '0' and @weektotal is not null
-----Monday-----
Insert into tmptimesheethours
(timesheetid,
applicantid,
workedon,
hoursworked,
performancevalueid,
breaks)
select IDENT_CURRENT('tmptimesheets'),
@userNM,
@workedon,
@hoursworked,
@performancevalueid,
'0'
FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t
WHERE p.placementid = t.placementid and p.applicantid = @userNM
AND t.createdon dateadd(day,-1,getdate())
and @hoursworked > '0' and @workedon = t.periodstarting
-----Tuesday------
Insert into tmptimesheethours
(timesheetid,
applicantid,
workedon,
hoursworked,
performancevalueid,
breaks)
select IDENT_CURRENT('tmptimesheets'),
@userNM,
dateadd(day,1,@workedon),
@hoursworkedtue,
@performancevalueid,
'0'
FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t
WHERE p.placementid = t.placementid and p.applicantid = @userNM
AND t.createdon dateadd(day,-1,getdate())
and @hoursworkedtue > '0'
and dateadd(day,1,@workedon) = dateadd(day,1,t.periodstarting)
----Wednesday------
Insert into tmptimesheethours
(timesheetid,
applicantid,
workedon,
hoursworked,
performancevalueid,
breaks)
select IDENT_CURRENT('tmptimesheets'),
@userNM,
dateadd(day,2,@workedon),
@hoursworkedwed,
@performancevalueid,
'0'
FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t
WHERE p.placementid = t.placementid and p.applicantid = @userNM
AND t.createdon dateadd(day,-1,getdate())
and @hoursworkedwed > '0'
and dateadd(day,2,@workedon) = dateadd(day,2,t.periodstarting)
--------Thurs-----
Insert into tmptimesheethours
(timesheetid,
applicantid,
workedon,
hoursworked,
performancevalueid,
breaks)
select IDENT_CURRENT('tmptimesheets'),
@userNM,
dateadd(day,3,@workedon),
@hoursworkedthurs,
@performancevalueid,
'0'
FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t
WHERE p.placementid = t.placementid and p.applicantid = @userNM
AND t.createdon dateadd(day,-1,getdate())
and @hoursworkedthurs > '0'
and dateadd(day,3,@workedon) = dateadd(day,3,t.periodstarting)
-------Friday------
Insert into tmptimesheethours
(timesheetid,
applicantid,
workedon,
hoursworked,
performancevalueid,
breaks)
select IDENT_CURRENT('tmptimesheets'),
@userNM,
dateadd(day,4,@workedon),
@hoursworkedfri,
@performancevalueid,
'0'
FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t
WHERE p.placementid = t.placementid and p.applicantid = @userNM
AND t.createdon dateadd(day,-1,getdate())
and @hoursworkedfri > '0'
and dateadd(day,4,@workedon) = dateadd(day,4,t.periodstarting)
---------sat--------
Insert into tmptimesheethours
(timesheetid,
applicantid,
workedon,
hoursworked,
performancevalueid,
breaks)
select IDENT_CURRENT('tmptimesheets'),
@userNM,
dateadd(day,5,@workedon),
@hoursworkedsat,
@performancevalueid,
'0'
FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t
WHERE p.placementid = t.placementid and p.applicantid = @userNM
AND t.createdon dateadd(day,-1,getdate())
and @hoursworkedsat >'0'and dateadd(day,5,@workedon) = dateadd(day,5,t.periodstarting)
--------sunday-------
Insert into tmptimesheethours
(timesheetid,
applicantid,
workedon,
hoursworked,
performancevalueid,
breaks)
select IDENT_CURRENT('tmptimesheets'),
@userNM,
dateadd(day,6,@workedon),
@hoursworkedsun,
@performancevalueid,
'0'
FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t
WHERE p.placementid = t.placementid and p.applicantid = @userNM
AND t.createdon dateadd(day,-1,getdate())
and @hoursworkedsun > '0'
and dateadd(day,6,@workedon) = dateadd(day,6,t.periodstarting)
COMMIT
END TRY
BEGIN CATCH
if @@error <> 0
ROLLBACK Transaction
END CATCH
April 11, 2008 at 12:00 pm
TRY ... CATCH catches all the errors with a severity greater than 10 at execution.
That means that syntacs erors will not be captured. Your "sp" contains several (like missed operand in comparison expression for dateadd() function).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply