SQL Table-Valued Functions

USE [Surgere1]
GO
/****** Object:  UserDefinedFunction [dbo].[fnAssetTranactionList]    Script Date: 04/27/2012 12:09:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[fnAssetTranactionList](@container_id bigint,@label_nbr varchar(10))
Returns  @tblAssetTransactionList Table
(

transaction_id bigint,
asset_id bigint,
container_id bigint,
client_id bigint,
from_id bigint,
to_id bigint,
supplier_id bigint,
Transaction_Dt datetime,
transaction_by varchar(50),
From_type varchar(50),
from_name varchar(50),
to_type varchar(50),
to_name varchar(50),
transaction_status varchar(50),
is_active_yn varchar(10),
inactive_Dt datetime

)
As
Begin
Declare @tblTemp table
(id bigint,supplier_locationname varchar(50))

if exists(select * from transactions
 where from_type='Manu' and to_type='Supplier' and
 asset_id in (select asset_id from asset where container_id=@container_id and
 label_nbr=@label_nbr)  )
Begin  
insert into @tblAssetTransactionList
select b.transaction_id,a.asset_id,b.container_id,b.client_id,b.from_id,
b.to_id,b.supplier_id,
b.created_dt,transaction_by,from_type,
c.manufacturer_name,to_type,d.Supplier_name,transaction_status,a.is_active_yn,
a.inactive_Dt
from asset  a
inner join transactions b on a.asset_id=b.asset_id
inner join manufacturer c on b.from_id=c.manufacturer_id
inner join Supplier d on d.Supplier_id=b.supplier_id
where a.container_id=@container_id and  a.label_nbr=@label_nbr
and from_type='Manu' and to_type='Supplier'
END
if exists(select * from transactions
where from_type='Supplier' and to_type='Facility' and
asset_id in (select asset_id from asset where container_id=@container_id and
label_nbr=@label_nbr)  )
Begin
insert into @tblAssetTransactionList
select  b.transaction_id,a.asset_id,b.container_id,b.client_id,b.from_id,
b.to_id,b.supplier_id,
b.created_dt,transaction_by,from_type,
cc.Supplier_name+' - '+ c.Location_name,to_type,d.facility_name,transaction_status,a.is_active_yn,
a.inactive_Dt
from asset  a
inner join transactions b on a.asset_id=b.asset_id
inner join Supplier_location c on b.from_id=c.Supplier_location_id
inner join Supplier cc on b.supplier_id=cc.Supplier_id
inner join Facility d on d.facility_id=b.to_id
where a.container_id=@container_id and  a.label_nbr=@label_nbr
and from_type='Supplier' and to_type='Facility'
End
if exists(select * from transactions
where from_type='Supplier' and to_type='Facility' and
asset_id in (select asset_id from asset where container_id=@container_id and
label_nbr=@label_nbr)  )
Begin
insert into @tblAssetTransactionList
select  b.transaction_id,a.asset_id,b.container_id,b.client_id,b.from_id,
b.to_id,b.supplier_id,
b.created_dt,transaction_by,from_type,
d.facility_name,to_type,cc.Supplier_name+' - '+ c.Location_name,transaction_status,a.is_active_yn,
a.inactive_Dt
from asset  a
inner join transactions b on a.asset_id=b.asset_id
inner join Supplier_location c on b.to_id=c.Supplier_location_id
inner join Supplier cc on b.supplier_id=cc.Supplier_id
inner join Facility d on d.facility_id=b.from_id
where a.container_id=@container_id and  a.label_nbr=@label_nbr
and from_type='Facility' and to_type='Supplier'
End
if exists(select * from transactions
where from_type='Client' and to_type='Repair' and
asset_id in (select asset_id from asset where container_id=@container_id and
label_nbr=@label_nbr)  )
Begin
insert into @tblAssetTransactionList
select  b.transaction_id,a.asset_id,b.container_id,b.client_id,b.from_id,
b.to_id,b.supplier_id,
b.created_dt,transaction_by,from_type,
'',to_type,'',transaction_status,a.is_active_yn,
a.inactive_Dt
from asset  a
inner join transactions b on a.asset_id=b.asset_id
--inner join Supplier_location c on b.to_id=c.Supplier_location_id
--inner join Supplier cc on b.supplier_id=cc.Supplier_id
--inner join Facility d on d.facility_id=b.from_id
where a.container_id=@container_id and  a.label_nbr=@label_nbr
and from_type='Client' and to_type='Repair'
End
if exists(select * from transactions
where from_type='Client' and to_type='Retire' and
asset_id in (select asset_id from asset where container_id=@container_id and
label_nbr=@label_nbr)  )
Begin
insert into @tblAssetTransactionList
select  b.transaction_id,a.asset_id,b.container_id,b.client_id,b.from_id,
b.to_id,b.supplier_id,
b.created_dt,transaction_by,from_type,
'',to_type,'',transaction_status,a.is_active_yn,
a.inactive_Dt
from asset  a
inner join transactions b on a.asset_id=b.asset_id
--inner join Supplier_location c on b.to_id=c.Supplier_location_id
--inner join Supplier cc on b.supplier_id=cc.Supplier_id
--inner join Facility d on d.facility_id=b.from_id
where a.container_id=@container_id and  a.label_nbr=@label_nbr
and from_type='Client' and to_type='Retire'
End
Return
End