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 
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