create a function returns a table (using cursor)

 

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 Begindeclare @p_first_weight decimal (10,2), @p_first_weighing_datetime datetime, @p_second_weight decimal (10,2), @p_second_weighing_datetime datetimedeclare cur_weigh cursor for select weight,weighing_datetime from dbo.tbl_weighingwhere weigh_code = @transaction_id order by weighing_datetime desc;declare @num intset @num = (select count(*) from dbo.tbl_weighingwhere weigh_code = @transaction_id )OPEN cur_weighIF (@num = 0) set @num = 0ELSEBEGINif (@num = 1) beginFETCH NEXT FROM cur_weigh into @p_second_weight,@p_second_weighing_datetimeInsert into @weighingInfo values (null,null,@p_second_weight,@p_second_weighing_datetime)endelseBEGINFETCH NEXT FROM cur_weigh into @p_second_weight,@p_second_weighing_datetimeWHILE @@FETCH_STATUS = 0beginFETCH NEXT FROM cur_weigh into @p_first_weight,@p_first_weighing_datetimebreak;endinsert into @weighingInfo values (@p_first_weight,@p_first_weighing_datetime,@p_second_weight,@p_second_weighing_datetime)ENDENDCLOSE cur_weighDEALLOCATE cur_weighreturn 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_weighingcross applydbo.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