How To Get Values From Queries, And Then Using In Another Query?

Hi

I have the following tables and stored procedure. I need to pass a value to
the stored procedure and have it use the value in a query. After running
that query it will return an ID which is then used in an insert statement.
At present the values in @MovieId int, @UserID int are left empty (see my
original code at the bottom of posting). I think this is due to an issue
with when the sql is executed (?).

I get the feeling I should have something like this instead, but not sure:

ALTER proc inserttransactions

@MovieName nvarchar(50) = 'team',
@uName nvarchar(50) = 'frank',
@FrameNumber int = 0

as

DECLARE @MovieId int, @UserID int

-- Find MovieID from MovieName
@MovieId = exec MovieName2Id @MovieName

-- Find UserID from UserEmail
@UserEmail = exec Email2UserId @uName

-- Insert Data
INSERT INTO Transactions(MovieId, UserId, FrameNumber)
VALUES (@MovieId, @UserID, @FrameNumber)


Thanks in advance.




========MY CODE=============
Tables:

CREATE TABLE [dbo].[movies] (
[movieID] [int] IDENTITY (1, 1) NOT NULL ,
[movieName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[movieDateAdded] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[transactions] (
[userID] [int] NULL ,
[movieID] [int] NULL ,
[FrameNumber] [int] NOT NULL ,
[transDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[users] (
[userID] [int] IDENTITY (1, 1) NOT NULL ,
[userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userDateRegistered] [datetime] NULL
) ON [PRIMARY]
GO

Stored Procedure:

ALTER proc inserttransactions

@MovieName nvarchar(50) = 'team',
@uName nvarchar(50) = 'frank',
@FrameNumber int = 0

as

DECLARE @MovieId int, @UserID int

-- Find MovieID from MovieName
SELECT @MovieId = MovieId
FROM Movies
WHERE MovieName = @MovieName

-- Find UserID from UserEmail
SELECT @UserID = UserID
FROM Users
WHERE UserEmail = @uName

-- Insert Data
INSERT INTO Transactions(MovieId, UserId, FrameNumber)
VALUES (@MovieId, @UserID, @FrameNumber)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO