Insert Query Inside a Function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert Query Inside a Function

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2011-02-07 : 20:39:23

Hi,

I have a function which does the match on first_name, last_name, middle_name and suffix.

The function returns the contact_id to be either 1 if there is an exact match, 0 if there is no match and -1 if there is more than one match. If there are more than one match we want to show the error report.

I am trying to apply insert inside a fuction which is wrong. I need a way to to insert in the error report table in this function.

If I come out of the function I will lose the data.

Here is the code:

ALTER FUNCTION [dbo].[fnMatchPersonInMbrsID00]
(
@club_id char(3),
@mbrs_id char(8),
@fname varchar(64),
@mname varchar(64),
@lname varchar(70),
@sname varchar(64))
RETURNS int
AS
BEGIN

declare @contact_id int
declare @cnt int

--
-- Return 0 if the club_id/mbrs_id do not exist at all
--
if NOT EXISTS (select mbrskey_id
from membership
where club_id = @club_id
and mbrs_id = @mbrs_id) return (0)

--
-- Look for an exact first/last name match
--
select @cnt = count(*),
@contact_id = max(contact_id)
from contact
where first_name = @fname
and last_name = @lname
and contact_id in (select contact_id from member
where club_id = @club_id
and mbrs_id = @mbrs_id)

--
-- If more than one match, try using middle name and suffix as well
--
if @cnt > 1
BEGIN
select @cnt = count(*),
@contact_id = max(contact_id)
from contact
where first_name = @fname
and ISNULL(middle_name,' ') = ISNULL(@mname,' ')
and last_name = @lname
and ISNULL(suffix,' ') = ISNULL(@sname,' ')
and contact_id in (select contact_id from member
where club_id = @club_id
and mbrs_id = @mbrs_id)
END

--
---- If no match default to the "00" member
----
--if @cnt = 0
--BEGIN
-- select @contact_id = contact_id
-- from member
-- where club_id = @club_id
-- and mbrs_id = @mbrs_id
-- and mbr_assoc_id = '00'
--END
--
----
---- If still more than one match then lowest active assoc_id
----
--if @cnt > 1
--BEGIN
-- select @contact_id = contact_id
-- from member
-- where club_id = @club_id
-- and mbrs_id = @mbrs_id
-- and mbr_assoc_id = (select min(mbr_assoc_id)
-- from member
-- where club_id = @club_id
-- and mbrs_id = @mbrs_id
-- and cancel_dt IS NULL
-- and mbr_assoc_id <> '00')
--END

if @cnt > 1
BEGIN

set @contact_id=-1 -----Too Many Matches-------

INSERT INTO dbo.Error_Report (Contact_Id,First_Name,Middle_Name,Last_Name,Membership_Id,Last_Source_System,user_entered)
(
select contact.contact_id,
contact.first_name,
contact.middle_name,
contact.last_name,
member.mbrs_id,
contact.last_source_system,
contact.user_entered
from contact,
member
where
member.contact_id = contact.contact_id
and contact.first_name = @fname
and ISNULL(contact.middle_name,' ') = ISNULL(@mname,' ')
and contact.last_name = @lname
and ISNULL(contact.suffix,' ') = ISNULL(@sname,' ')
and member.club_id = @club_id
and member.mbrs_id = @mbrs_id)
--select @cnt = count(*),
-- @contact_id = max(contact_id)
-- from contact
-- where first_name = @fname
-- and ISNULL(middle_name,' ') = ISNULL(@mname,' ')
-- and last_name = @lname
-- and ISNULL(suffix,' ') = ISNULL(@sname,' ')
-- and contact_id in (select contact_id from member
-- where club_id = @club_id
-- and mbrs_id = @mbrs_id)
END

return (ISNULL(@contact_id,0))
END

Please let me know how can I achieve this.

Thank you,

Dp

dim
Yak Posting Veteran

57 Posts

Posted - 2011-02-07 : 20:41:14

I have removed the commented lines above for better understanding.

ALTER FUNCTION [dbo].[fnMatchPersonInMbrsID00]
(
@club_id char(3),
@mbrs_id char(8),
@fname varchar(64),
@mname varchar(64),
@lname varchar(70),
@sname varchar(64))
RETURNS int
AS
BEGIN

declare @contact_id int
declare @cnt int

--
-- Return 0 if the club_id/mbrs_id do not exist at all
--
if NOT EXISTS (select mbrskey_id
from membership
where club_id = @club_id
and mbrs_id = @mbrs_id) return (0)

--
-- Look for an exact first/last name match
--
select @cnt = count(*),
@contact_id = max(contact_id)
from contact
where first_name = @fname
and last_name = @lname
and contact_id in (select contact_id from member
where club_id = @club_id
and mbrs_id = @mbrs_id)

--
-- If more than one match, try using middle name and suffix as well
--
if @cnt > 1
BEGIN
select @cnt = count(*),
@contact_id = max(contact_id)
from contact
where first_name = @fname
and ISNULL(middle_name,' ') = ISNULL(@mname,' ')
and last_name = @lname
and ISNULL(suffix,' ') = ISNULL(@sname,' ')
and contact_id in (select contact_id from member
where club_id = @club_id
and mbrs_id = @mbrs_id)
END

if @cnt > 1
BEGIN
set @contact_id=-1 -----Too Many Matches-------
INSERT INTO dbo.Error_Report (Contact_Id,First_Name,Middle_Name,Last_Name,Membership_Id,Last_Source_System,user_entered)
(
select contact.contact_id,
contact.first_name,
contact.middle_name,
contact.last_name,
member.mbrs_id,
contact.last_source_system,
contact.user_entered
from contact,
member
where
member.contact_id = contact.contact_id
and contact.first_name = @fname
and ISNULL(contact.middle_name,' ') = ISNULL(@mname,' ')
and contact.last_name = @lname
and ISNULL(contact.suffix,' ') = ISNULL(@sname,' ')
and member.club_id = @club_id
and member.mbrs_id = @mbrs_id)

END

return (ISNULL(@contact_id,0))
END

Dp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources