Returning multiple variables from a function

Question

Returning multiple variables from a function

FredS-1001785

Mr or Mrs. 500

Points: 564
More actions
November 20, 2009 at 7:25 am

#216975

I need a scalar function that does not access tables, but rather is a series of case statements. After I have determined answer I would like to return two different variables.
Since I don't know how to do that I wrote this as a table based function, but I don't know how to get the answer out
For instance the following function returns the latest modification datetime and who did it.
FUNCTION [dbo].[DL_LastLastMod]
(
@L_modDT1 datetime
,@L_modBY1 char(20)
,@L_modDT2 datetime
,@L_modBY2 char(20)
,@L_modDT3 datetime
,@L_modBY3 char(20)
,@LM_key int
)
RETURNS @LastMods TABLE
(
last_modDT datetime
,last_modBY char(20)
,LM_key int
)
AS
begin
DECLARE @RetDate datetime
DECLARE @RetBy char(20)
-- First two variable sets
set @RetDate = case
when @L_modDT1 is null and @L_modDT2 is null then NULL
when @L_modDT1 is NULL then @L_modDT2
when @L_modDT2 is NULL then @L_modDT1
when @L_modDT1 < @L_modDT2 then @L_modDT2
else @L_modDT1
end;
set @RetBy = case
when @L_modDT1 is null and @L_modDT2 is null then ''
when @L_modDT1 is NULL then @L_modBY2
when @L_modDT2 is NULL then @L_modBY1
when @L_modDT1 < @L_modDT2 then @L_modBY2
else @L_modBY1
end;
-- Third Variable set
set @RetDate = case
when @RetDate is null and @L_modDT3 is null then NULL
when @RetDate is NULL then @L_modDT3
when @L_modDT3 is NULL then @RetDate
when @RetDate < @L_modDT3 then @L_modDT3
else @RetDate
end;
set @RetBy = case
when @RetDate is null and @L_modDT3 is null then ''
when @RetDate is NULL then @L_modBY3
when @L_modDT3 is NULL then @RetBy
when @RetDate < @L_modDT3 then @L_modBY3
else @RetBy
end;
INSERT INTO @LastMods
(last_modDT
,last_modBY
,LM_key)
VALUES
(@RetDate, @RetBy, @LM_key)
RETURN
END
This is a small example where I would like to add a call to my function to return the last_modDT and lastmodBy for the latest of the two.
IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL
DROP TABLE #t1;
GO
IF OBJECT_ID(N'tempdb..#t2', N'U') IS NOT NULL
DROP TABLE #t2;
GO
SELECT 'Hello;' as dat1,
'2007/01/01T15:30' as mod1,
'bigDaddy' as modBY
,4 as iKey into #T1
select 'Goodbye;' as dat1,
'2007/15/01T02:10' as modDT,
'George' as modBY2
,4 as iKey into #T2
select T.dat1, T.mod1, T.modBY
,t2.modDT, t2.modBY2
from #T1 as T inner join #T2 as t2
ON T.iKey = t2.iKey

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

You don't need a function for this... use the last query in your code example and mix the case statments into that so you do things quickly in a set based manner.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Forget the function, you dont need it. Is all you want the last 3 edits ?

You havent supplied enough details for me to give an exact solution but something like this would be best...

Select * from Article

cross apply (Select top (3) *

from ArticleEdits

where ArticleEdits.ArticleID = Article.ArticleID

order by EditDate Desc)

Clear Sky SQL
My Blog[/url]