CODEWITHSUNDEEP
sql-servert-sql
user3085636
user3085636

Reputation: 15

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)

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

Upvotes: 0

Views: 4160

Answers (4)

Milen
Milen

Reputation: 8867

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

Upvotes: 0

simon at rcl
simon at rcl

Reputation: 7344

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 -

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41549

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.

Upvotes: 2

spetzz
spetzz

Reputation: 679

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

Upvotes: -1

Related Questions