SQL Server How do I make this code loop 7 times and go to the next row?

zynd9foi 于 2023-04-19 发布在 Go

关注(0)|答案(2)|浏览(100)

I've been learning SQL by myself, and I'm stumped on this one.

In this table I'm working on, I have a persons first name and last name as a column, and I'm using a user defined function to concat the two columns to get their full name.

What I have works for one column, but I don't know how to loop it. Here's my code.

CREATE FUNCTION dbo.fullName(
    @firstName VARCHAR(10),
    @lastName VARCHAR(10))
RETURNS VARCHAR(20)
AS 
BEGIN
    DECLARE @fullName VARCHAR(20)
    SELECT @fullName = CONCAT(firstName, ' ', lastName) FROM stray_kids
    RETURN @fullName;   
END

Then when I call it with SELECT lastName, firstName,dbo.fullName(firstName, lastName) FROM stray_kids It comes up with the image I added, which is great for the first row and then I then tried to add a while loop and counters and such but couldn't figure it out. Also, how would I name the new column name?

Thanks if you can help! <3 my output

sql-server

来源：https://stackoverflow.com/questions/76032231/how-do-i-make-this-code-loop-7-times-and-go-to-the-next-row

2条答案

按热度按时间

8yparm6h1#

The functionality you are after is easily achieved using a computed column

alter table stray_kids
add FullName as CONCAT(firstName, ' ', lastName);

Then you can simply

select FullName from stray_kids;

Newer versions of SQL Server also support concat_ws(' ', FirstName, LastName);

If you really wanted to use a function, all you need is

CREATE FUNCTION dbo.fullName(
    @firstName VARCHAR(10),
    @lastName VARCHAR(10))
RETURNS VARCHAR(21)
AS 
BEGIN
  RETURN CONCAT(@firstName, ' ', @lastName);   
END

Note the return type needs to account for the additional space character... 10 chars seems al ittle short though so perhaps use varchar(50)

赞(0）回复(0）举报 2023-04-19

iovurdzv2#

To apply your dbo.fullName function to all rows in the stray_kids table, you can use the SELECT statement with the FROM clause to query the table and apply the function to each row:

SELECT firstName, lastName, dbo.fullName(firstName, lastName) AS fullName
FROM stray_kids;

This will create a new column named fullName in the result set that contains the full name for each person.

You don't need to use a loop or counters in this case because the SELECT statement already loops over all rows in the table.

But remember that using a function here is not an optimal solution. Use a simple concatenation.

赞(0）回复(0）举报 2023-04-19

我来回答

SQL Server How do I make this code loop 7 times and go to the next row?

2条答案

相关问题

热门标签

最新问答