May 23, 2006 at 4:02 pm
Hello,
I have a function called fn_OrderAllowed that has a return variable called @allow. When I execute the function, the return value is not being returned. I have tried a couple of different things, and maybe it just needs 'an extra pair of eyes' to see the problem. Below is the function;
***********************************
ALTER FUNCTION dbo.fn_OrderAllowed
(
@account bigint,
@company smallint,
@seq int,
@tdate datetime
)
RETURNS bit
AS
BEGIN
declare @allow bit
set @allow = 1
if @seq = 187
begin
declare @asmt_complete_date datetime
declare @phq9_order_date datetime
--get the date of the last completed bh assessment
select @asmt_complete_date = max(end_date)
--from ah_member_assessments
from ah_member_assessments
where account = @account
and company = @company
and assessment_versionid in (122, 154, 750) --bh assessment or depccip assessment (member should never have both)
and end_date is not null
and statusid = 3 --completed assessment
--get the date of the last completed phq9 mailing
select @phq9_order_date = max(done_date)
from ah_member_order (nolock)
Where account = @account
and company = @company
and seq = 187 --phq9 order
--and status = 4 --completed
--if the assessment hasn't been completed, we don't want to create the order
if @asmt_complete_date is null
begin
set @allow = 0
end
else begin
if @asmt_complete_date is not null and @phq9_order_date is not null
begin
--don't allow the order if one has been created since the last saved assessment
if convert(varchar(24), @asmt_complete_date, 113) < convert(varchar(24), @phq9_order_date, 113)
and datediff(d, @phq9_order_date, @tdate) < 60
--and dateadd(dd, 60, isnull(@phq9_order_date, '1-1-2950')) < @tdate
begin
set @allow = 0
end
end
end
end
return @allow
END
************************************
What could the problem be?
Thank you for your help!
CSDunn
May 23, 2006 at 6:04 pm
Could you also post the code that calls the function and the code that checks the return?
May 23, 2006 at 6:28 pm
@phq9_order_date is null
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 24, 2006 at 7:55 am
This is the code that calls the function;
create procedure dbo.spv_MO_InsertOrder
@company smallint,
@account bigint,
@seq int,
@status int = 3,
@user_code nvarchar(20),
@contact_type int,
@done_date datetime = null,
@harvest_date datetime = null,
@inscode_id int = null,
@category nvarchar(50) = null,
@odesc nvarchar(80) = null,
@comments nvarchar(255) = null,
@document_id int = null,
@order_date datetime = null,
@file_id int = null,
@attempts smallint = null,
@expire_date datetime = null,
@re_order bit = 0,
@instance smallint = null,
@previous_order_action nvarchar(1) = null,
@orig_seq int = null,
@Origin nvarchar(50) = null,
@Allowed_Instances bit = 1,
@id bigint = null output
AS
BEGIN
declare @tdate datetime
declare @counter bigint
set @tdate = getdate()
if @previous_order_action is not null
BEGIN
print 'do something here later'
END
if dbo.fn_OrderAllowed(@account, @company, @seq, @tdate) = 1
BEGIN
if dbo.fn_spim_is_offered_for_member(@company, @account, @seq, @tdate) = 'Y'
BEGIN
if @inscode_id is null
-- get the member's inscode
BEGIN
select @inscode_id = inscode_id
from ah_member_coverage with (nolock)
where account = @account
and company = @company
and isprimary = 'Y'
END
if @ODesc is null
BEGIN
select @ODesc = d.Name
from AH_Intervention_Desc d (nolock)
where d.SEQ = @SEQ
END
declare @insertable bit
set @insertable = 0 --Assume order is not insertable
if @document_id is not null
BEGIN
set @insertable = 1
END
else
BEGIN
if @Allowed_Instances = 1
BEGIN
if not exists (select company
from ah_member_order o with (nolock)
where account = @account
and company = @company
and seq = @seq
and status = 3
-- Additional subquery to look only at the most recent instance of this seq.
-- Calvin Bottoms 7/5/04 -- CR 5492
and not exists(
select company
from ah_member_order
where account = o.account
and company = o.company
and seq = o.seq
and order_date > o.order_date
)
)
BEGIN
set @insertable = 1
END
END
else
BEGIN
if not exists (select o.Account
from AH_Member_Order o (nolock)
where o.Account = @account
and o.Company = @Company
and o.SEQ = @SEQ
and o.Status IN (2,3,4)
)
BEGIN
set @insertable = 1
END
END
END
if @insertable = 1
BEGIN
insert ah_member_order(
company,
account,
seq,
contact_type,
done_date,
harvest_date,
inscode_id,
category,
odesc,
comments,
document_id,
order_date,
file_id,
created_by,
last_edit_user_code,
attempts,
status,
expire_date,
re_order,
instance,
last_edit_date,
orig_seq,
Origin
)
select @company,
@seq,
@contact_type,
@done_date,
@harvest_date,
@inscode_id,
@category,
case
when @odesc is not null then @odesc
else name
end,
@comments,
@document_id,
case
when @order_date is null then getdate()
else @order_date
end,
@file_id,
@user_code,
@user_code,
isnull(@attempts, 0),
case @status
when null then 4 -- Uncompleted
else @status
end,
@expire_date,
@re_order,
@instance,
getdate(),
@orig_seq,
@Origin
from ah_intervention_desc with (nolock)
where seq = @seq
set @id = scope_identity()
END
END
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
May 24, 2006 at 8:48 am
I think that your problem might be that @phq9_order_date is null.
It is populated by:
from ah_member_order (nolock)
Where account = @account
and company = @company
and seq = 187 --phq9 order
Your code doesn't handle the possibility that @phq9_order_date is null:
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 24, 2006 at 9:07 am
Is the problem that the function doesn't return anything? Or that it always returns 1?
Even stax68's suggestion would only point to an area in which the function is possibly erroneously returning 1.
I don't see any issues with your code off-hand. My suggestion would be to go through and start simplifying your code until either it works, or you see the nature of the error. Then, build it back up and see what is causing it to break.
A couple side notes:
You test for "@asmt_complete_date is null" and then in the ELSE clause test for "is not null." This is superfluous, as the fact that you are even evaluating the ELSE clause means that @asmt_complete_date is not null.
Why do you convert your datetime values to varchar values to do a simple comparison? Not only is that unnecessary overhead, but it is potentially less accurate. You are essentially comparing which date comes first alphabetically.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply