June 13, 2008 at 7:36 am
In Oracle, the developer created a monster stored function and wants me to convert it to SQL. SSMA barfed on it. Here's what I have so far. It parses but does not compile:
create FUNCTION [dbo].[F_GET_ACTIVITY_STATE]
( /* pass in an Activity ID */ @nActivityID float(53) )
RETURNS varchar(max)
AS
BEGIN
DECLARE
@vStatus varchar(10),@nSuspenseDurationDays numeric(4),
@nOccurenceDurationDays numeric(4),
@nSuspenseDurationHours numeric(4,2),
@nOccurenceDurationHours numeric(4,2),
@dCreatedDate datetime, @dCompletedDate datetime
DECLARE
@return_value_argument varchar(max)
SELECT pac.completed_date as dCompletedDate,
pac.created_date as dCreatedDate,
pacd.suspense_duration_days as nSuspenseDurationDays,
pacd.occurrence_duration_days as nOccurenceDurationDays,
pacd.suspense_duration_hours as nSuspenseDurationHours,
pacd.occurrence_duration_hours as nOccurenceDurationHours
FROM
prc_activity_defs pacd,
prc_activities pac
WHERE
pac.id = @nActivityID and
pac.prc_activity_defs_id = pacd.id
if (nOccurenceDurationDays is null or nOccurenceDurationDays = 0 )
if ( nOccurenceDurationHours is null or nOccurrenceDurationHours = 0 )
if(dCompletedDate is null)
BEGIN
set @vStatus = N'Open'
return @vStatus
END
else
if (dCreatedDate + ISNULL(nSuspenseDurationDays, 0 )
+ ISNULL( nOccurenceDurationDays, 0 )
+ ISNULL( nSuspenseDurationHours, 0 )/24
+ ISNULL( nOccurenceDurationHours, 0 )/24 ) > getdate()
and dCompletedDate is null
BEGIN
set @vStatus = N'Open'
return @vStatus
END
else
if ( dCreatedDate + ISNULL(nSuspenseDurationDays, 0 )
+ ISNULL(nOccurenceDurationDays, 0 )
+ ISNULL(nSuspenseDurationHours, 0 )/24
+ ISNULL(nOccurenceDurationHours, 0 )/24 < getdate() )
and dCompletedDate is null
BEGIN
set @vStatus = N'Overdue'
return @vStatus
END
else
if dCompletedDate is not null
set @vStatus = N'Complete'
return @vStatus
end
June 13, 2008 at 10:09 am
Never mind, I fixed it.
Doc
🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply