CODEWITHSUNDEEP
sqlsql-servert-sqlsql-function
PriceCheaperton
PriceCheaperton

Reputation: 5349

query multiple values in function and return inside 1 variable

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

Upvotes: 2

Views: 431

Answers (2)

Zzz
Zzz

Reputation: 3025

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;

Upvotes: 3

Cortright
Cortright

Reputation: 1174

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.

Upvotes: 2

Related Questions