query multiple values in function and return inside 1 variable

Question

I would like to write a function which query's the database for:

SELECT m.title, m.firstname, m.surname 
FROM contact c
LEFT JOIN membership m
ON c.contactID=m.contactID

You see i could have many contacts returned from the above query. I would like to return the results into a variable in the function so that I may use globally.

I want the results of the function to show up like this:

Mr John Test
Mrs Jane Smith

I want to write a function which returns @MemberNames like the example with John and Jane...

This is what i have attempted so far:

CREATE FUNCTION fnGetMemberNames 
    (   
    @membershipID int,    
    )
RETURNS int
AS

BEGIN
-- Declare the return variable here
DECLARE @MemberNames varchar(300)

SELECT m.title, m.firstname, m.surname 
FROM contact c
LEFT JOIN membership m
ON c.contactID=m.contactID
WHERE membershipID=@membershipID

RETURN @MemberNames

END

I'm not 100% sure what you are looking for with your question. Could you elaborate a little on what the rows in the database look like and what exactly you want the output to be? Thanks! — David Ziemann
– David Ziemann, Commented Jun 13, 2013 at 21:31
I want to write a function which returns @MemberNames like the example with John and Jane... — PriceCheaperton
– PriceCheaperton, Commented Jun 13, 2013 at 21:33
Ahh, well it seems as though @Azzi has the right idea with his answer. You can concat the select items into one full name — David Ziemann
– David Ziemann, Commented Jun 13, 2013 at 21:40

Zzz · Accepted Answer · 2013-06-13 21:57:55Z

3

You have to change your select statement as follows and then create a function that returns this value:

SELECT (m.title + ' ' + m.firstname + ' ' + m.surname) 
FROM contact c
LEFT JOIN membership m
ON c.contactID=m.contactID

The example above uses string concatenation and creates a single column under the column heading Name from multiple columns, with the title of the person followed by a space, then the first name of the person followed by a space, then last name.

Your function will look something like this:

CREATE FUNCTION your_function (@ID INT)
RETURNS VARCHAR(50)
BEGIN
  DECLARE @name_to_return AS VARCHAR(50);
  SELECT  @name_to_return =
             (m.title + ' ' + m.firstname + ' ' + m.surname) 
  FROM contact c
  LEFT JOIN membership m
    ON c.contactID=@ID;
  RETURN @name_to_return ;
END;

edited Jun 13, 2013 at 21:57

answered Jun 13, 2013 at 21:31

Zzz

3,0655 gold badges41 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zzz Over a year ago

@PriceCheaperton Didn't see your edit until I gave my answer. You are missing several ';' and your return type is not correct. Check the template I gave you.

PriceCheaperton Over a year ago

Thanks but how do i call this function or use it in a query window?

Zzz Over a year ago

@PriceCheaperton SELECT function(args...) AS result

Cortright · Accepted Answer · 2013-06-13 21:54:51Z

2

Making this an answer because the @ variable was being interpreted as a @user directive...

@PriceCheaperton -- See what Azzi wrote:
You need to set your return variable equal to a value. Right now you're just declaring it as an empty varchar.

SELECT @MemberNames = (m.title + ' ' + m.firstname + ' ' + m.surname) FROM 
// the rest of your query here

...like Azzi said.

answered Jun 13, 2013 at 21:54

Cortright

1,1746 silver badges20 bronze badges

Collectives™ on Stack Overflow

query multiple values in function and return inside 1 variable

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related