SQL Stored Procedure - how do I get a row and use it inside my procedure?

I am not completely sure how to articulate this, so please be sure to ask me to clarify something if it isn't clear. Here is what I want to do:

ALTER PROCEDURE UpdateUserProfiles
    @userId UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON;

    SET oldUser = (SELECT TOP 1 
        FirstName
        FROM OldUsers WHERE UserId = @userId);

    UPDATE NewUsers
    SET FirstName = oldUser.FirstName开发者_开发问答
    WHERE Id = @userId;

END
GO

Now obviously this does not work, but how do I go about using a row inside my SP? Thank you.

You can drop the usage of the stored procedure and do this instead:

UPDATE NewUsers
SET FirstName = (SELECT TOP 1 FirstName
                 FROM OldUsers WHERE UserId = @userId)
WHERE Id = @userId;

Update

Since you are updating multiple columns, you are better off using a joined updated:

update usr
set nus.Firstname = ous.FirstName,
    nus.Lastname = ous.LastName
from NewUsers nus 
inner join OldUsers ous
  on nus.UserId = ous.UserId
where nus.UserId = @userId

Original Issue

Even though the suggestions here solved your problem, we never really told you what was wrong with your stored procedure. Well, you don't need it any more, but there's always time to learn! Here's the fixed version of the (now deprecated) stored procedure:

ALTER PROCEDURE UpdateUserProfiles
    @userId UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON;

    declare @oldUser UNIQUEIDENTIFIER;
    SET @oldUser = (SELECT TOP 1 
        FirstName
        FROM OldUsers WHERE UserId = @userId);

    UPDATE NewUsers
    SET FirstName = @oldUser
    WHERE Id = @userId;

END
GO

You were missing the declaration of @oldUser. Also, @oldUser only holds the FirstName, so oldUser.FirstName did not make sense.

Does this help you ?

UPDATE u
SET FirstName = o.FirstName 
FROM NewUsers U
JOIN 
OldUsers o on
o.Id = u.id
WHERE 
U.ID = @userId

Here is a variation of Adrians script that can update all rows at once:

UPDATE u
SET FirstName = 
(select top 1 FirstName   
from OldUsers 
where UserId = u.UserId
)
FROM NewUsers u