share a simple function for household to display name1 name2

Hi All,

Good day.

here is a simple function for convert household customer_no to first name (or last name)  for A1(or A2).

select [dbo].[LF_ReturnName](3573,'last name','name2')
this will return last name of A2 in household account 3573.

have fun

Ben

USE [impresario]
GO
/****** Object:  UserDefinedFunction [dbo].[LF_ReturnName]    Script Date: 08/14/2012 05:10:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,Ben GU>
-- Create date: <Create Date,14-Aug-12>
-- Description: <Create this function for return last name or first name for name1 or name2 >
----
-- =============================================

/*

select [dbo].[LF_ReturnName](3573,'last name','name2')
select [dbo].[LF_ReturnName](3573,'first name','name2')
select [dbo].[LF_ReturnName](3573,'first name','name1')
select [dbo].[LF_ReturnName](3573,'last name','name1')

select * from T_AFFILIATION where ( group_customer_no=3573 or individual_customer_no=3573)  and primary_ind='Y'
select * from T_CUSTOMER where customer_no=3573

Copyleft, this is free, and you can modify it.

*/

ALTER FUNCTION [dbo].[LF_ReturnName]
(
 @HH int,  ---household customer_no
 @nameType varchar(50), ---last name or first name
 @InType varchar(50)  ---name1 or name2
)
RETURNS varchar(200)
AS
BEGIN
 declare @myNameReturn varchar(200)
 declare @tmp varchar(200)

 set @tmp=null

if @nameType='last name'
begin
if @InType='name1'
 begin
  select @tmp=lname from T_CUSTOMER where customer_no in(select individual_customer_no from T_AFFILIATION
  where group_customer_no=@HH and primary_ind='Y' and name_ind=-1)
 end
else if @InType='name2'
 begin
  select @tmp=lname from T_CUSTOMER where customer_no in(select individual_customer_no from T_AFFILIATION
  where group_customer_no=@HH and primary_ind='Y' and name_ind=-2)
 end

end

if @nameType='first name'
begin
if @InType='name1'
 begin
  select @tmp=fname from T_CUSTOMER where customer_no in(select individual_customer_no from T_AFFILIATION
  where group_customer_no=@HH and primary_ind='Y' and name_ind=-1)
 end
else if @InType='name2'
 begin
  select @tmp=fname from T_CUSTOMER where customer_no in(select individual_customer_no from T_AFFILIATION
  where group_customer_no=@HH and primary_ind='Y' and name_ind=-2)
 end
end

set @myNameReturn=@tmp

 RETURN @myNameReturn

END