ALTER FUNCTION [dbo].[GetWeighingInfoByTransactionId]
(
-- Add the parameters for the function here
@transaction_id UniqueIdentifier
)
RETURNS @weighingInfo table (first_weight decimal (10,2),first_weighing_datetime datetime, second_weight decimal (10,2),second_weighing_datetime datetime)
AS
Begin
declare @p_first_weight decimal (10,2), @p_first_weighing_datetime datetime, @p_second_weight decimal (10,2), @p_second_weighing_datetime datetime
declare cur_weigh cursor for
select weight,weighing_datetime from dbo.tbl_weighing
where weigh_code = @transaction_id order by weighing_datetime desc;
declare @num int
set @num = (select count(*) from dbo.tbl_weighing
where weigh_code = @transaction_id )
OPEN cur_weigh
IF (@num = 0)
set @num = 0
ELSE
BEGIN
if (@num = 1)
begin
FETCH NEXT FROM cur_weigh into @p_second_weight,@p_second_weighing_datetime
Insert into @weighingInfo values (null,null,@p_second_weight,@p_second_weighing_datetime)
end
else
BEGIN
FETCH NEXT FROM cur_weigh into @p_second_weight,@p_second_weighing_datetime
WHILE @@FETCH_STATUS = 0
begin
FETCH NEXT FROM cur_weigh into @p_first_weight,@p_first_weighing_datetime
break;
end
insert into @weighingInfo values (@p_first_weight,@p_first_weighing_datetime,@p_second_weight,@p_second_weighing_datetime)
END
END
CLOSE cur_weigh
DEALLOCATE cur_weigh
return
End
How to use it:
select * from dbo.GetWeighingInfoByTransactionId ('836CE377-E7AD-4E25-ADAF-0C44742BD402')
Call the function join with other table (Cross Apply) , the parameter is in the selected result and it be will pass into the function every row.
select distinct weigh_code,s.*
from tbl_weighing
cross apply
dbo.GetWeighingInfoByTransactionId(weigh_code) s
http://msdn.microsoft.com/en-us/library/ms131103.aspx
http://www.sqlteam.com/article/returning-complex-data-from-user-defined-functions-with-cross-apply