How do I return multiple values from a function and how to invoke the function and assign the values returned to local variables? (T-SQL)

Question

I'm new to SQL Server and I would like to ask a query Suppose I have a function which returns four values like e.g.

set @RetValue=@PendingFlag + @Status + @BTFound + @ValueDate and RETURN @RetValue. Now suppose im invoking this function from another function like suppose:

DECLARE @d VARCHAR(max)
SET@d=dbo.ValidateBTtype(@BType,@ReadBT,@Type,@Id,@ClientId,@Customerno,@InterestDate,@ApplicationType)

In @d il have all the four values But what I want to do is I want the returned values and I want to store it separately in local variables like local variable @PendingFlag=returned value (@Pending Flag) local variable @Status=returned value (@Status) and etc..

Please help me how to store the returned values separately in the second function by invoking the first function

Are you using SQL Server? (or MySQL or something) – Jon Egerton Jan 03 '14 at 11:03 — Jon Egerton, Jan 03 '14 at 11:03

score 2 · Accepted Answer · answered Jan 03 '14 at 10:59

2

You could return a table from the function, where the table has one row, and four columns, one for each of the return values.

See here for details on table valued functions.

answered Jan 03 '14 at 10:59

Jon Egerton

40,401
11
97
129

Milen · Answer 2 · 2014-01-03T11:05:30.653

0

possible approaches here:
in your dbo.ValidateBTtype function to split the four strings (or values) with comma and then split the @d string in to 4 parts using comma as delimiter like so:
@RetValue= @PendingFlag + ',' + @Status + ',' + @BTFound + ','+ @ValueDate

...or create table valued function instead..

edited Jan 03 '14 at 11:05

answered Jan 03 '14 at 10:59

Milen

8,697
7
43
57

score 0 · Answer 3 · edited May 23 '17 at 12:03

0

See here 'How to return multiple values from a sql server function'. Or google 'sql server returning multiple values from function' and take your pick.

Cheers -

edited May 23 '17 at 12:03

Community

1
1

answered Jan 03 '14 at 11:00

simon at rcl

7,326
1
17
24

score -1 · Answer 4 · answered Jan 03 '14 at 10:58

-1

You need to look into SQL Concepts .
Use SQlClient Class for making an SQL query .
Look into DataSet class for it is usually used to store "multiple return values"
http://www.dotnetperls.com/sqlclient

answered Jan 03 '14 at 10:58

spetzz

679
8
19

You can't use a data set in a stored procedure, so this doesn't help. That;'s probably why you've been down voted. – simon at rcl Jan 03 '14 at 11:01

How do I return multiple values from a function and how to invoke the function and assign the values returned to local variables? (T-SQL)

4 Answers4