From: migurus@yahoo.com   
      
   I have a table with input data and a temporal table with methods to be used.   
   The input data is used to calculate results based on method, which is time   
   dependant. The results are several int values. I don't know how to organize my   
   calculation code -    
   function or SP? and how to do it in my select?   
      
   Please see pseudo-code below:   
      
   create procedure RUN -- or should it be function?   
   	@METHOD int,   
   	@INPUT1	int,   
   	@INPUT2	int,   
   	@VAL1	int	OUTPUT,   
   	@VAL2	int	OUTPUT   
   as    
   begin   
   -- calculate VAL1 and VAL2 according to @METHOD   
   -- I don't show my if/else etc here for clarity   
   	set @VAL1 = @INPUT1;   
   	set @VAL2 = @INPUT2;   
   end;   
   GO   
      
   declare	@EXPERIMENTS table (   
   	EXP_DATE	date,   
   	EXP_DATA1	int,   
   	EXP_DATA2	int   
   );   
   declare @METHODS table (   
   	EFF_DATE	date,   
   	PARAM1		int,   
   	PARAM2		int   
   );   
   insert into @EXPERIMENTS(EXP_DATE,EXP_DATA1,EXP_DATA2)    
   values   
   ('20131201', 100, 200),   
   ('20140201', 100, 200),   
   ('20140310', 100, 201);   
      
   insert into @METHODS (EFF_DATE, PARAM1, PARAM2)    
   values   
   ('20130101', 1, 0),   
   ('20140101', 2, 200);   
      
   select    
   	E.EXP_DATE,   
   	E.EXP_DATA1,   
   	E.EXP_DATA2,   
   	M.PARAM1 [METHOD USED],   
   	-- I need to call RUN(M.EFF_DATE, M.PARAM1, E.EXP_DATA1, E.EXP_DATA2)   
   	-- and show here both results   
   	--RESULT_1, ? how   
   	--RESULT_2 ? how   
   from    
   	@EXPERIMENTS E,   
   	@METHODS M   
   where M.EFF_DATE <= E.EXP_DATE   
   and M.EFF_DATE=(select MAX(EFF_DATE) from @METHODS where EFF_DATE <=   
   E.EXP_DATE)   
      
      
   I expect to get following as result:   
      
   EXP_DATE DATA1 DATA2 METHOD_USED RESULT1 RESULT2   
   2013-1201, 100 200   1           100     100   
   2014-0201, 100 200   2           120     500   
   2014-0310, 100 201   2           120     502   
      
   My difficulty is that SP can return output parameters, but it seems I can't   
   call SP in select? The UDF can be called from select but can not return more   
   than one value and I need to return at least two, in fact 4 values, I just   
   simplified everything.   
      
   Any ideas would be much appreciated.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)