查询函数中的多个值，并在一个变量中返回

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

我想写一个返回@MemberNames的函数，比如John和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

2 个解决方案

#1

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

您必须如下所示更改select语句，然后创建一个函数，返回此值:

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.

上面的示例使用字符串连接，并在来自多个列的列标题名下创建一个列，其中person的标题后面跟着一个空格，然后person的第一个名称后面跟着一个空格，然后是姓。

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;

#2

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

之所以这样做是因为@变量被解释为一个@user指令……

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

@PriceCheaperton——看看Azzi所写的:您需要将返回变量设置为一个值。现在你只是宣布它为一个空varchar。

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

...like Azzi said.

…像Azzi说。

#1