Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1727

help with output parameters needed

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

help with output parameters needed migurus <migurus@yahoo.com> - 2014-03-12 20:41 -0700
  Re: help with output parameters needed Ross Presser <rpresser@gmail.com> - 2014-03-12 21:01 -0700
  Re: help with output parameters needed Erland Sommarskog <esquel@sommarskog.se> - 2014-03-13 08:40 +0100

csiph-web